topic Re: Creating multiple rows from 1 row of data in SAS Programming

Creating multiple rows from 1 row of data

Xinxin — Thu, 25 Oct 2012 20:27:25 GMT

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

Re: Creating multiple rows from 1 row of data

PGStats — Thu, 25 Oct 2012 20:43:41 GMT

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;

Re: Creating multiple rows from 1 row of data

RichardinOz — Thu, 25 Oct 2012 21:33:27 GMT

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

Re: Creating multiple rows from 1 row of data

RichardinOz — Thu, 25 Oct 2012 22:49:51 GMT

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

Re: Creating multiple rows from 1 row of data

Miracle — Fri, 26 Oct 2012 00:14:32 GMT

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;

Re: Creating multiple rows from 1 row of data

art297 — Fri, 26 Oct 2012 00:15:53 GMT

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.

Re: Creating multiple rows from 1 row of data

Tom — Fri, 26 Oct 2012 02:49:04 GMT

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.

Re: Creating multiple rows from 1 row of data

RichardinOz — Fri, 26 Oct 2012 04:26:53 GMT

Tom

Check the input vector. Question appears in the input header, with null values in the data supplied.

Richard in Oz

Re: Creating multiple rows from 1 row of data

Xinxin — Fri, 26 Oct 2012 17:36:03 GMT

Arthur, that is correct.

Re: Creating multiple rows from 1 row of data

Xinxin — Fri, 26 Oct 2012 17:38:24 GMT

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.