|
|
|
||||
|
|
|||||
This link will take you to a SAS macro called %tolong for reshaping data from wide format to long format. This link will take you to the help file.
Sometimes you need to reshape your data which is in a wide format (like shown below)
wide famid faminc96 faminc97 faminc98 1 40000 40500 41000 2 45000 45400 45800 3 75000 76000 77000
into a long format (like shown below).
long famid year faminc 1 96 40000 1 97 40500 1 98 41000 2 96 45000 2 97 45400 2 98 45800 3 96 75000 3 97 76000 3 98 77000
The wide record has one record per family, the long record has one record per year (per family). Doing this kind of reshaping in SAS can be tricky. We have written a macro (a program) to help you do this kind of standard data reshaping. In order to use this program, you need to be able to provide some key information about your data to be reshaped. Let's use the examples of "wide" and "long" above to illustrate. You need to be able to supply seven pieces of information:
The program below uses those seven pieces of information in the %tolong macro.
data wide; input famid faminc96 faminc97 faminc98 ; cards; 1 40000 40500 41000 2 45000 45400 45800 3 75000 76000 77000 ; run; %tolong(wide,long,famid,year,96,98,faminc);
A portion of the output from the program above is shown below.
OBS FAMID YEAR FAMINC 1 1 96 40000 2 1 97 40500 3 1 98 41000 4 2 96 45000 5 2 97 45400 6 2 98 45800 7 3 96 75000 8 3 97 76000 9 3 98 77000
Even though this is just subset of the output, you can see that the data were properly reshaped. You can look at each observation from wide in the program and see that it has been properly reshaped into a long data file in long.
Let's have a look at the complete output of this program. There are two parts to the output:
1. A proc print of a sample of observations (up to 10) from the wide data file and then a proc print of the corresponding records from the long data file.
2. A proc means of the wide records and a corresponding proc means of the long records.
It is important to inspect the proc print and proc means to check to see if the data were properly reshaped.
1A. Verify reshaping of wide to long using PROC PRINT of 10 wide records Compare this print of the wide data (wide1) below with the PROC PRINT of the long data (long1) in Step 1B below PROC PRINT of WIDE data file wide1 OBS FAMID FAMINC96 FAMINC97 FAMINC98 1 1 40000 40500 41000 2 2 45000 45400 45800 3 3 75000 76000 77000 1B. Verify reshaping of wide to long using PROC PRINT of 10 wide records. Compare this print of the long data below (long1) with the PROC PRINT of the wide data (wide1) in step 1A above. PROC PRINT of LONG data file long1 OBS FAMID YEAR FAMINC 1 1 96 40000 2 1 97 40500 3 1 98 41000 4 2 96 45000 5 2 97 45400 6 2 98 45800 7 3 96 75000 8 3 97 76000 9 3 98 77000
You can compare the proc print from 1A (the wide data) with the proc print of 1B (the long data) to look for any problems or errors in reshaping the data. For this small dataset, you can verify every record. In more realistic situations, this will be just a small fraction of the records.
2A. Verify reshaping of wide to long using proc means Compare this PROC MEANS for the wide data (wide1) with the PROC MEANS of the long data (long1) in step 2B below PROC MEANS of WIDE data file wide1 Variable N Mean Std Dev Minimum Maximum ------------------------------------------------------------------- FAMINC96 3 53333.33 18929.69 40000.00 75000.00 FAMINC97 3 53966.67 19238.07 40500.00 76000.00 FAMINC98 3 54600.00 19546.87 41000.00 77000.00 ------------------------------------------------------------------- 2B. Verify reshaping of wide to long using PROC MEANS Compare this PROC MEANS for the long data (long1) below with the PROC MEANS of the wide data (wide1) in step 2A above PROC MEANS of LONG data file long1 Analysis Variable : FAMINC YEAR N Obs N Mean Std Dev Minimum Maximum ------------------------------------------------------------------------------ 96 3 3 53333.33 18929.69 40000.00 75000.00 97 3 3 53966.67 19238.07 40500.00 76000.00 98 3 3 54600.00 19546.87 41000.00 77000.00 ------------------------------------------------------------------------------
The output from the proc means can be used to more fully compare the entire wide file with the long file. You can see that the mean, standard deviation, minimum and maximum of FAMINC96 (from 2A) is the same as the mean of FAMINC for YEAR=96 (from 2B). Likewise you can see that the values for 97 and 98 are the same for 2A and 2B.
Reshaping two variables is not much harder than reshaping one variable. The only difference is that you specify the two variables to be reshaped where you had just specified the one variable. The example below shows how you can reshape two variables.
data wide2; input famid faminc96 faminc97 faminc98 spend96 spend97 spend98 ; cards; 1 40000 40500 41000 38000 39000 40000 2 45000 45400 45800 42000 43000 44000 3 75000 76000 77000 70000 71000 72000 ;run; * notice that where we had just faminc is now faminc spend ; %tolong(wide2,long2,famid,year,96,98,faminc spend);
The output of the program above is shown below.
1A. Verify reshaping of wide to long using PROC PRINT of 10 wide records Compare this print of the wide data (wide2) below with the PROC PRINT of the long data (long2) in Step 1B below PROC PRINT of WIDE data file wide2 OBS FAMID FAMINC96 FAMINC97 FAMINC98 SPEND96 SPEND97 SPEND98 1 1 40000 40500 41000 38000 39000 40000 2 2 45000 45400 45800 42000 43000 44000 3 3 75000 76000 77000 70000 71000 72000 1B. Verify reshaping of wide to long using PROC PRINT of 10 wide records Compare this print of the long data below (long2) with the PROC PRINT of the wide data (wide2) in step 1A above. PROC PRINT of LONG data file long2 OBS FAMID YEAR FAMINC SPEND 1 1 96 40000 38000 2 1 97 40500 39000 3 1 98 41000 40000 4 2 96 45000 42000 5 2 97 45400 43000 6 2 98 45800 44000 7 3 96 75000 70000 8 3 97 76000 71000 9 3 98 77000 72000 2A. Verify reshaping of wide to long using proc means Compare this PROC MEANS for the wide data (wide2) with the PROC MEANS of the long data (long2) in step 2B below PROC MEANS of WIDE data file wide2 Variable N Mean Std Dev Minimum Maximum ------------------------------------------------------ FAMINC96 3 53333.33 18929.69 40000.00 75000.00 SPEND96 3 50000.00 17435.60 38000.00 70000.00 FAMINC97 3 53966.67 19238.07 40500.00 76000.00 SPEND97 3 51000.00 17435.60 39000.00 71000.00 FAMINC98 3 54600.00 19546.87 41000.00 77000.00 SPEND98 3 52000.00 17435.60 40000.00 72000.00 ------------------------------------------------------ 2B. Verify reshaping of wide to long using PROC MEANS Compare this PROC MEANS for the long data (long2) below with the PROC MEANS of the wide data (wide2) in step 2A above PROC MEANS of LONG data file long2 YEAR N Obs Variable N Mean Std Dev Minimum ------------------------------------------------------- 96 3 FAMINC 3 53333.33 18929.69 40000.00 SPEND 3 50000.00 17435.60 38000.00 97 3 FAMINC 3 53966.67 19238.07 40500.00 SPEND 3 51000.00 17435.60 39000.00 98 3 FAMINC 3 54600.00 19546.87 41000.00 SPEND 3 52000.00 17435.60 40000.00 >------------------------------------------------------- YEAR N Obs Variable Maximum ------------------------------------------- 96 3 FAMINC 75000.00 SPEND 70000.00 97 3 FAMINC 76000.00 SPEND 71000.00 98 3 FAMINC 77000.00 SPEND 72000.00 -------------------------------------------
The proc print and proc means results indicate that the reshape was successful. The log also includes the information shown below to help you confirm that you provided the correct information about how the data should be reshaped. This summary looks correct.
------------------------------------------------------------------ - Reshape Wide to Long: Actions to take summarize below ------------------------------------------------------------------ <- Input (wide) data file is : wide2 - Output (long) data file is : long2 - Variable that uniquely identifies wide records is : famid - Variable name for suffix of wide variable is : year - and can range from: 96 to 98 - Wide variables to long variables... - faminc96 ... faminc98 -> faminc of type N and length 8 - spend96 ... spend98 -> spend of type N and length 8
The previous examples had one variable which uniquely identified the wide records (famid). Consider the data file shown below. The data shows the heights of children in a family at 1 year of age ht1 and at 2 years of age ht2. The wide records are uniquely identified by famid and birth.
FAMID BIRTH HT1 HT2 1 1 2.8 3.4 1 2 2.9 3.8 1 3 2.2 2.9 2 1 2.0 3.2 2 2 1.8 2.8 2 3 1.9 2.4 3 1 2.2 3.3 3 2 2.3 3.4 3 3 2.1 2.9
The program below shows that it is quite easy to reshape this kind of data file.
data wide3; input famid birth ht1 ht2 ; cards; 1 1 2.8 3.4 1 2 2.9 3.8 1 3 2.2 2.9 2 1 2.0 3.2 2 2 1.8 2.8 2 3 1.9 2.4 3 1 2.2 3.3 3 2 2.3 3.4 3 3 2.1 2.9 ; run;
* note that famid birth are the variables that ; * uniquely identify the wide records ;
%tolong(wide3,long3,famid birth,age,1,2,ht);
We will forego showing the output, but you can run the program yourself to verify that the proc print and proc means indicate that the data was reshaped properly.
For simplicity, the prior examples were artificially small and simple. Let's look at an example that is a bit more realistic. This example has 50 records and six columns of data in the wide record that need to be made long. (This example is still quite small, but it at least allows us to see how useful the proc means can be for verifying a file which cannot be verified by just inspecting the proc print.)
The program below reshapes the wide file called wide4 to be a long file called long4.
data wide4; input id inc90 inc91 inc92 inc93 inc94 inc95 ; cards; 1 66483 69146 74643 79783 81710 86143 2 17510 17947 19484 20979 21268 22998 3 57947 62964 68717 70957 75198 75722 4 64831 71060 71918 72514 73100 74379 5 18904 19949 21335 22237 23829 23913 6 32057 34770 35834 37387 40899 42372 7 60551 64869 67983 70498 71253 75177 8 16553 18189 18349 19815 21739 22980 9 32611 33465 35961 36416 37183 40627 10 61379 66002 67936 70513 74405 76009 11 24065 24229 25709 26121 26617 28142 12 32975 36185 37601 41336 43399 43670 13 69548 71341 72455 76552 80538 85330 14 50274 53349 55900 59375 61216 63911 15 72011 73334 76248 77724 78638 80582 16 18911 20046 21343 21630 22330 23081 17 68841 75410 80806 81327 81571 86499 18 28099 30716 32986 36097 39124 39866 19 17302 18778 18872 19884 20665 21855 20 16291 16674 16770 17182 17979 18917 21 43244 46545 47633 50744 54734 59075 22 56393 59120 60801 61404 63111 69278 23 47347 49571 50101 51345 56463 56927 24 16076 17217 17296 17900 18171 18366 25 65906 69679 76131 77676 81980 85426 26 58586 61188 66542 69267 71063 74549 27 61674 66584 69185 75193 78647 81898 28 31673 31883 32774 34485 36929 39751 29 63412 67593 69911 73092 80105 81840 30 27684 28439 30861 31406 32960 35530 31 71873 76449 80848 88691 94149 97431 32 62177 63812 64235 65703 69985 71136 33 37684 38258 39208 39489 39745 41236 34 64013 66398 71877 75610 76395 79644 35 16011 16847 17746 19123 19183 19996 36 49215 52195 52343 56365 58752 59354 37 15774 16643 17605 18781 18996 19685 38 29106 31693 31852 34505 35806 36179 39 25147 26923 28785 30987 34036 34106 40 71978 79144 80453 86580 95164 96155 41 46166 47579 49455 53849 56630 57473 42 55810 59443 65291 66065 69009 74365 43 49642 50603 53917 54858 58470 59767 44 21348 22361 23412 24038 24774 25828 45 44361 48720 51356 54927 56670 58800 46 56509 60517 61532 65077 69594 73089 47 39097 40293 43237 44809 48782 53091 48 18685 19405 20165 20316 22197 23557 49 73103 76243 76778 82734 86279 86784 50 48129 49267 53799 58768 63011 66461 ; run;
%tolong(wide4,long4,id,year,90,95,inc);
The output of this program is shown below.
1A. Verify reshaping of wide to long using PROC PRINT of 10 wide records Compare this print of the wide data (wide4) below with the PROC PRINT of the long data (long4) in Step 1B below PROC PRINT of WIDE data file wide4 OBS ID INC90 INC91 INC92 INC93 INC94 INC95 1 1 66483 69146 74643 79783 81710 86143 2 2 17510 17947 19484 20979 21268 22998 3 3 57947 62964 68717 70957 75198 75722 4 4 64831 71060 71918 72514 73100 74379 5 5 18904 19949 21335 22237 23829 23913 6 6 32057 34770 35834 37387 40899 42372 7 7 60551 64869 67983 70498 71253 75177 8 8 16553 18189 18349 19815 21739 22980 9 9 32611 33465 35961 36416 37183 40627 10 10 61379 66002 67936 70513 74405 76009 1B. Verify reshaping of wide to long using PROC PRINT of 10 wide records. Compare this print of the long data below (long4) with the PROC PRINT of the wide data (wide4) in step 1A above. PROC PRINT of LONG data file long4 OBS ID YEAR INC 1 1 90 66483 2 1 91 69146 3 1 92 74643 4 1 93 79783 5 1 94 81710 6 1 95 86143 7 2 90 17510 8 2 91 17947 9 2 92 19484 10 2 93 20979 11 2 94 21268 12 2 95 22998 13 3 90 57947 14 3 91 62964 15 3 92 68717 16 3 93 70957 17 3 94 75198 18 3 95 75722 19 4 90 64831 20 4 91 71060 21 4 92 71918 22 4 93 72514 23 4 94 73100 24 4 95 74379 25 5 90 18904 26 5 91 19949 27 5 92 21335 28 5 93 22237 29 5 94 23829 30 5 95 23913 31 6 90 32057 32 6 91 34770 33 6 92 35834 34 6 93 37387 35 6 94 40899 36 6 95 42372 37 7 90 60551 38 7 91 64869 39 7 92 67983 40 7 93 70498 41 7 94 71253 42 7 95 75177 43 8 90 16553 44 8 91 18189 45 8 92 18349 46 8 93 19815 47 8 94 21739 48 8 95 22980 49 9 90 32611 50 9 91 33465 51 9 92 35961 52 9 93 36416 53 9 94 37183 54 9 95 40627 55 10 90 61379 56 10 91 66002 57 10 92 67936 58 10 93 70513 59 10 94 74405 60 10 95 76009
The proc print (parts 1A and 1B above) suggests that the reshape was successful, but it is based only on the first 10 wide records. It is quite possible that there could be errors in other parts of the file. The proc means (parts 2A and 2B) become more important to examine in this case.
2A. Verify reshaping of wide to long using proc means Compare this PROC MEANS for the wide data (wide4) with the PROC MEANS of the long data (long4) in step 2B below PROC MEANS of WIDE data file wide4 Variable N Mean Std Dev Minimum Maximum -------------------------------------------------------------------- INC90 50 43899.32 19523.39 15774.00 73103.00 INC91 50 46380.70 20749.43 16643.00 79144.00 INC92 50 48519.58 21720.12 16770.00 80848.00 INC93 50 50842.28 22780.12 17182.00 88691.00 INC94 50 53289.02 23824.01 17979.00 95164.00 INC95 50 55379.00 24592.83 18366.00 97431.00 -------------------------------------------------------------------- 2B. Verify reshaping of wide to long using PROC MEANS Compare this PROC MEANS for the long data (long4) below with the PROC MEANS of the wide data (wide4) in step 2A above PROC MEANS of LONG data file long4 Analysis Variable : INC YEAR N Obs N Mean Std Dev Minimum Maximum ------------------------------------------------------- 90 50 50 43899.32 19523.39 15774.00 73103.00 91 50 50 46380.70 20749.43 16643.00 79144.00 92 50 50 48519.58 21720.12 16770.00 80848.00 93 50 50 50842.28 22780.12 17182.00 88691.00 94 50 50 53289.02 23824.01 17979.00 95164.00 95 50 50 55379.00 24592.83 18366.00 97431.00 --------------------------------------------------------
The proc means shows no discrepancies between the wide and long data file (parts 2A and 2B below). This gives us more confidence that the reshape was successful.
If you have a large number of wide variables, the proc print of the long data can be very long even for just 10 variables. The %tolong macro allows you to increase or decrease the number of wide observations that are printed (which alters the corresponding number of long observations as well). If you wanted to just print five wide observations (and then the corresponding number of long observations), you can use the numprint option as shown below.
< data step creating wide4 would go here. it is omitted to save space >
%tolong(wide4,long5,id,year,90,95,inc,numprint=5);
If you want to suppress printing of the proc print and the proc means entirely, you can do so using the quiet=yes option as shown in the program below. We don't generally recommend this, we give you the option trusting that you would only use this option when it is safe to do so.
< data step creating wide4 would go here. it is omitted to save space >
%tolong(wide4,long5,id,year,90,95,inc,quiet=yes);
The %tolong macro assumes that all of your wide variables to be converted to long are numeric variables. Suppose you have some character variables as well, as in the example below.
F F F A A A S S S M M M P P P D D D F I I I E E E E E E A N N N N N N B B B M C C C D D D T T T I 9 9 9 9 9 9 9 9 9 D 6 7 8 6 7 8 6 7 8 40000 40500 41000 38000 39000 40000 yes yes no 2 45000 45400 45800 42000 43000 44000 yes no no 3 75000 76000 77000 70000 71000 72000 no no no
The variable faminc is numeric and the variable spend is numeric, but the variable debt is character. The example below shows how to handle reshaping with character variables using the types= option. As you see below, the types=N N C option is used to indicate that the first variable (faminc) is numeric, the second variable (spend) is numeric and the third variable (debt) is character.
data wide7; input famid faminc96 faminc97 faminc98 spend96 spend97 spend98 debt96 $ debt97 $ debt98 $ ; cards; 1 40000 40500 41000 38000 39000 40000 yes yes no 2 45000 45400 45800 42000 43000 44000 yes no no 3 75000 76000 77000 70000 71000 72000 no no no ; run;
%tolong(wide7,long7,famid,year,96,98,faminc spend debt,types=N N C);
Below, we show just the output of the proc print (parts 1A and 1B below) showing that the character variables were properly reshaped as well as the numeric variables.
F F F A A A S S S M M M P P P D D D F I I I E E E E E E A N N N N N N B B B O M C C C D D D T T T B I 9 9 9 9 9 9 9 9 9 S D 6 7 8 6 7 8 6 7 8 1 1 40000 40500 41000 38000 39000 40000 yes yes no 2 2 45000 45400 45800 42000 43000 44000 yes no no 3 3 75000 76000 77000 70000 71000 72000 no no no 1B. Verify reshaping of wide to long using PROC PRINT of 10 wide records. Compare this print of the long data below (long7) with the PROC PRINT of the wide data (wide7) in step 1A above. PROC PRINT of LONG data file long7 OBS FAMID YEAR FAMINC SPEND DEBT 1 1 96 40000 38000 yes 2 1 97 40500 39000 yes 3 1 98 41000 40000 no 4 2 96 45000 42000 yes 5 2 97 45400 43000 no 6 2 98 45800 44000 no 7 3 96 75000 70000 no 8 3 97 76000 71000 no 9 3 98 77000 72000 no
Consider the example data file below. It is just like the previous example, except the character variable is city instead of debt, and some of the values of city are longer than eight characters.
F F F A A A S S S M M M P P P C C C F I I I E E E I I I A N N N N N N T T T O M C C C D D D Y Y Y B I 9 9 9 9 9 9 9 9 9 S D 6 7 8 6 7 8 6 7 8 1 1 40000 40500 41000 38000 39000 40000 Sacramento Sacramento Placerville 2 2 45000 45400 45800 42000 43000 44000 Denver Denver Seattle 3 3 75000 76000 77000 70000 71000 72000 Malibu Ventura Ventura
The %tolong macro assumes that the character variables are a length of eight or shorter unless you tell it otherwise. We will intentionally omit this option in the example below to show how your character variables can get truncated.
data wide6; length city96 city97 city98 $ 12 ; input famid faminc96 faminc97 faminc98 spend96 spend97 spend98 city96 $ city97 $ city98 $ ; cards; 1 40000 40500 41000 38000 39000 40000 Sacramento Sacramento Placerville 2 45000 45400 45800 42000 43000 44000 Denver Denver Seattle 3 75000 76000 77000 70000 71000 72000 Malibu Ventura Ventura ; run;
%tolong(wide6,long6,famid,year,96,98,faminc spend city,types=N N C);
We show just the output of the proc print for the long data showing how the character variables for city were truncated to a length of eight.
OBS FAMID YEAR FAMINC SPEND CITY 1 1 96 40000 38000 Sacramen 2 1 97 40500 39000 Sacramen 3 1 98 41000 40000 Placervi 4 2 96 45000 42000 Denver 5 2 97 45400 43000 Denver 6 2 98 45800 44000 Seattle 7 3 96 75000 70000 Malibu 8 3 97 76000 71000 Ventura 9 3 98 77000 72000 Ventura
The example below uses the lengths=8 8 12 option is used to indicate that the first two variables (faminc spend) should be a length of eight and the third variable (city) should be a length of 12. When using the lengths= option it is safest to supply a value of 8 for all numeric variables.
data wide7; length city96 city97 city98 $ 12 ; input famid faminc96 faminc97 faminc98 spend96 spend97 spend98 city96 $ city97 $ city98 $ ; cards; 1 40000 40500 41000 38000 39000 40000 Sacramento Sacramento Placerville 2 45000 45400 45800 42000 43000 44000 Denver Denver Seattle 3 75000 76000 77000 70000 71000 72000 Malibu Ventura Ventura ; run;
%tolong(wide7,long7,famid,year,96,98,faminc spend city,types=N N C,lengths=8 8 12);
We show just the output of the proc print for the long data below showing how the character variables for city were properly reshaped by using the lengths= option.
OBS FAMID YEAR FAMINC SPEND CITY 1 1 96 40000 38000 Sacramento 2 1 97 40500 39000 Sacramento 3 1 98 41000 40000 Placerville 4 2 96 45000 42000 Denver 5 2 97 45400 43000 Denver 6 2 98 45800 44000 Seattle 7 3 96 75000 70000 Malibu 8 3 97 76000 71000 Ventura 9 3 98 77000 72000 Ventura
The %tolong macro can be used to reshape data from wide format to long format for many basic situations. An example of the syntax is given below.
data wide1; input famid faminc96 faminc97 faminc98 ; cards; 1 40000 40500 41000 2 45000 45400 45800 3 75000 76000 77000 ; run;
%tolong(wide1,long1,famid,year,96,98,faminc);
Where
wide1 - is the input wide data set
long1 - is the output long data set
famid - is the variable that uniquely identifies the wide records.
year - is the variable that will be created in the long data file with 96 97 and 98
96 - is the lowest value of year
98 - is the highest value of year
faminc - is the name of the variable to be reshaped from wide to long.The %tolong macro also supports the following options (illustrated by example)
Here are other examples of the options.
numprint=5 To print only five wide records (default is 10)
quiet=yes To suppress printing of proc print and proc means
types=N N C Indicates types of the variables: the third is character.
lengths= 8 8 12 Indicates the lengths of the variables to be 8 8 and 12
%tolong(wide7,long7,famid,year,96,98,faminc spend city,types=N N C); %tolong(wide7,long7,famid,year,96,98,faminc spend city,types=N N C,lengths=8 8 12);
%tolong(wide7,long7,famid,year,96,98,faminc spend,numprint=5);
%tolong(wide7,long7,famid,year,96,98,faminc spend,quiet=yes);
These examples cover situations where there are no complications. However, look out for these complications:
UCLA Researchers are invited to our Statistical Consulting Services
We recommend others to our list of Other Resources for Statistical Computing Help
These pages are Copyrighted (c) by UCLA Academic Technology Services