BookmarkSubscribeRSS Feed
superbug
Quartz | Level 8

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
...

 

2 REPLIES 2
Tom
Super User Tom
Super User

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.

superbug
Quartz | Level 8

@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?

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

SAS Training: Just a Click Away

 Ready to level-up your skills? Choose your own adventure.

Browse our catalog!

Discussion stats
  • 2 replies
  • 716 views
  • 1 like
  • 2 in conversation