My data is like this: (partial data)
CDC | BUS_UNIT_CODE | CUST_ID | LAST_DATE_RESP | QUESTION | SCORE | FINAL_STORE | Q1 | Q2 | Q3 | Q4 | Q5 | Q6 | Q7 | Q10 | Q7B | Q11 | Q9 | Q12 | Q14 | Q16 | Q17 | Q19 | Q21 | Q28B | Q24_34_1 | QAST1 | QAST2 | QAST3 | QAST4 | Q24_34_2 | QAST5 | Q24_34_3 | QAST6 | Q25 | Q26 | Q27 | Q27C | Q28 | Q28A | Q29A | Q29C | Q30A |
F | 95001 | 00630000303734-20100803 | 20100803 | 44063 | 1 | 1 | 1 | 4 | 5 | 1 | 4 | 4 | 3 | 5 | 4 | 2 | 4 | 1 | 1 | 1 | 2 | 3 | 1 | 1 | 3 | 9 | 1 | |||||||||||||||
F | 95001 | 00740000206306-20100803 | 20100804 | 44074 | 1 | 1 | 1 | 4 | 4 | 2 | 4 | 2 | 2 | 4 | 4 | 5 | 5 | 3 | 4 | 2 | 2 | 1 | 2 | 1 | 2 | 2 | 3 | 1 | 4 | 1 | 2 | 2 | 6 | 9 | 1 | |||||||
F | 95001 | 01420000200302-20100804 | 20100804 | 44142 | 1 | 1 | 2 | 5 | 5 | 1 | 5 | 5 | 5 | 5 | 5 | 3 | 5 | 1 | 4 | 2 | 2 | 1 | 2 | 6 | 9 | 2 | ||||||||||||||||
F | 95001 | 01570000406053-20100804 | 20100804 | 44157 | 1 | 1 | 1 | 4 | 5 | 9 | 5 | 5 | 5 | 5 | 2 | 5 | 2 | 2 | 2 | 2 | 2 | 2 | 2 | 6 | 9 | 2 |
I want to make it like this: (partial view)
CDC | BUS_UNIT | CUST_ID | LAST_DATE_RESP | QUESTION | SCORE | FINAL_STORE |
F | 95001 | 00630000303734-20100803 | 20100803 | Q1 | 1 | 44063 |
F | 95001 | 00630000303734-20100803 | 20100803 | Q2 | 1 | 44063 |
F | 95001 | 00630000303734-20100803 | 20100803 | Q3 | 44063 | |
F | 95001 | 00630000303734-20100803 | 20100803 | Q4 | 1 | 44063 |
F | 95001 | 00630000303734-20100803 | 20100803 | Q5 | 4 | 44063 |
F | 95001 | 00630000303734-20100803 | 20100803 | Q6 | 5 | 44063 |
F | 95001 | 00630000303734-20100803 | 20100803 | Q7 | 1 | 44063 |
F | 95001 | 00630000303734-20100803 | 20100803 | Q10 | 44063 | |
F | 95001 | 00630000303734-20100803 | 20100803 | Q7B | 44063 | |
F | 95001 | 00630000303734-20100803 | 20100803 | Q11 | 44063 | |
F | 95001 | 00630000303734-20100803 | 20100803 | Q9 | 4 | 44063 |
F | 95001 | 00630000303734-20100803 | 20100803 | Q12 | 4 | 44063 |
F | 95001 | 00630000303734-20100803 | 20100803 | Q14 | 3 | 44063 |
F | 95001 | 00630000303734-20100803 | 20100803 | Q16 | 5 | 44063 |
F | 95001 | 00630000303734-20100803 | 20100803 | Q17 | 4 | 44063 |
F | 95001 | 00630000303734-20100803 | 20100803 | Q19 | 2 | 44063 |
Input and output will be .csv or ascii files but have to do it in SAS. I tried the trailing @ but could not get it.
Thanks in advance for your help !!
Use the following transformation :
data want(keep=CDC BUS_UNIT CUST_ID LAST_DATE_RESP QUESTION SCORE FINAL_STORE);
set MyData;
array myQ{*} Q:;
do i = 1 to dim(myQ);
question=vname(myQ{i});
score = myQ{i};
output;
end;
run;
PG
PG
array myQ{*} Q:;
is going to include QUESTION and any other variable beginning with Q
and will fail if any of them are character
Richard in Oz
Presumably you know in advance which are the columns you want to use, so I would suggest refining PGstats response
%Let QVars = Q1 Q2 Q3 Q4 Q5 Q6 Q7 Q10 Q7B Q11 Q9 /* ... etc */ ;
data want (drop = &Qvars)
set MyData;
array myQ{*} &QVars ;
do i = 1 to dim(myQ);
question=vname(myQ{i});
score = myQ{i};
output;
end;
run;
Richard in Oz
The OP will have to say but, my guess, is that Question and Score are not really in the original data and that all of the scores are numeric.
Arthur, that is correct.
Actually which variables are included in an array defined by a variable list (like Q:) will vary based on where in the datastep the array statement is placed. In this case the variable Question is referenced for the first time after the array statement so it will not be included.
Tom
Check the input vector. Question appears in the input header, with null values in the data supplied.
Richard in Oz
Thank you, PGStats. Your code worked along with the macro variable suggested by RichardinOz.....Thank you both, and to all the others who gave their valuable input !! I really appreciate it.
Hi Xinxin, how about this?
proc transpose data=partialdata out=transpose(rename=(COL1=Score _NAME_=Question) where=(Question ne "QUESTION") drop=_LABEL_);
by CDC BUS_UNIT_CODE CUST_ID LAST_DATE_RESP FINAL_STORE;
var Q:;
run;
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 16. Read more here about why you should contribute and what is in it for you!
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.
Select SAS Training centers are offering in-person courses. View upcoming courses for: