topic Re: proc transpose data from long back to wide in SAS Programming

proc transpose data from long back to wide

superbug — Tue, 27 Oct 2020 18:21:14 GMT

My data has 1,201,250 rows, part of the data is as below.

In each form, each examinee take the same 250 item. There are a few same itemID across different forms. e.g. item pp012 in both AAA and BBB, pp012 and pp024 in both BBB and CCC. there are 1350 unique items across all forms. I want to transpose this data back to wide to make a SPARSE matrix in terms of raw. The desired SPARSE matrix should have 1350 columns in terms of raw score. Any help would be much appreciated.

DATA have ; 
  INPUT form examineeID _NAME_   itemID  raw; 
CARDS ; 
AAA 111 raw_1  pp012   0 
AAA 111 raw_2  pp014   1
AAA 111 raw_3  pp017   1 
AAA 111 raw_4  pp015   0 
..
AAA 444 raw_1  pp012   1 
AAA 444 raw_2  pp014   0 
AAA 444 raw_3  pp017   1 
AAA 444 raw_4  pp015   1
...
BBB 777  raw_1 pp012   0
BBB 777  raw_2 pp024   1
BBB 777  raw_3 pp027   0
BBB 777  raw_4 pp025   1
...
BBB 999  raw_1 pp012   1
BBB 999  raw_2 pp024   1
BBB 999  raw_3 pp027   0
BBB 999  raw_4 pp025   0
....
CCC 666  raw_1 pp012   0
CCC 666  raw_2 pp024   1
CCC 666  raw_3 pp037   0
CCC 666  raw_4 pp035   1
...
CCC 888  raw_1 pp012   1
CCC 888  raw_2 pp024   1
CCC 888  raw_3 pp037   0
CCC 888  raw_4 pp035   0
...

Re: proc transpose data from long back to wide

Tom — Tue, 27 Oct 2020 18:35:57 GMT

As long as the total length of FORM and ITEM is never more than 32 characters it should be simple.

proc transpose data=have  out=want(drop=_name_)  delim=_;
  by examineeID ;
  id form itemID;
  var raw ;
run;

Not sure what the extra variable _NAME_ is for in your source data. The output of PROC TRANSPOSE will create a variable called _NAME_ to hold the name of the analysis variable (RAW in this case). That is not useful so I asked it to be dropped.

Re: proc transpose data from long back to wide

superbug — Tue, 27 Oct 2020 18:52:49 GMT

@Tom

Thank you!

I tried as you suggested. In the "want" result data, the order of the itemID is not as I desired. The itemID should be in ascending order in the resulted "want" data. How should I modify your code?