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
...
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.
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?
It's finally time to hack! Remember to visit the SAS Hacker's Hub regularly for news and updates.
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.
Ready to level-up your skills? Choose your own adventure.