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?
April 27 – 30 | Gaylord Texan | Grapevine, Texas
Walk in ready to learn. Walk out ready to deliver. This is the data and AI conference you can't afford to miss.
Register now and lock in 2025 pricing—just $495!
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.