Creating multiple rows from 1 row of data

Reply
Contributor
Posts: 35

Creating multiple rows from 1 row of data

My data is like this: (partial data)

CDCBUS_UNIT_CODECUST_IDLAST_DATE_RESPQUESTIONSCOREFINAL_STOREQ1Q2Q3Q4Q5Q6Q7Q10Q7BQ11Q9Q12Q14Q16Q17Q19Q21Q28BQ24_34_1QAST1QAST2QAST3QAST4Q24_34_2QAST5Q24_34_3QAST6Q25Q26Q27Q27CQ28Q28AQ29AQ29CQ30A
F9500100630000303734-20100803201008034406311145144354241112311391
F9500100740000206306-2010080320100804440741114424224455342212122314122691
F9500101420000200302-2010080420100804441421125515555535142212692
F9500101570000406053-2010080420100804441571114595555252222222692

I want to make it like this: (partial view)

CDCBUS_UNITCUST_IDLAST_DATE_RESPQUESTIONSCOREFINAL_STORE
F9500100630000303734-2010080320100803Q1144063
F9500100630000303734-2010080320100803Q2144063
F9500100630000303734-2010080320100803Q3             44063
F9500100630000303734-2010080320100803Q4144063
F9500100630000303734-2010080320100803Q5444063
F9500100630000303734-2010080320100803Q6544063
F9500100630000303734-2010080320100803Q7144063
F9500100630000303734-2010080320100803Q10             44063
F9500100630000303734-2010080320100803Q7B             44063
F9500100630000303734-2010080320100803Q11             44063
F9500100630000303734-2010080320100803Q9444063
F9500100630000303734-2010080320100803Q12444063
F9500100630000303734-2010080320100803Q14344063
F9500100630000303734-2010080320100803Q16544063
F9500100630000303734-2010080320100803Q17444063
F9500100630000303734-2010080320100803Q19244063

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

Respected Advisor
Posts: 4,644

Re: Creating multiple rows from 1 row of data

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
Super Contributor
Posts: 644

Re: Creating multiple rows from 1 row of data

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

Super Contributor
Posts: 644

Re: Creating multiple rows from 1 row of data

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

PROC Star
Posts: 7,360

Re: Creating multiple rows from 1 row of data

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.

Contributor
Posts: 35

Re: Creating multiple rows from 1 row of data

Arthur, that is correct.

Super User
Super User
Posts: 6,499

Re: Creating multiple rows from 1 row of data

Actually which variables are included in an array defined by a variable list (like QSmiley Happy 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.

Super Contributor
Posts: 644

Re: Creating multiple rows from 1 row of data

Tom

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

Richard in Oz

Contributor
Posts: 35

Re: Creating multiple rows from 1 row of data

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.

Regular Contributor
Posts: 215

Re: Creating multiple rows from 1 row of data

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;

Ask a Question
Discussion stats
  • 9 replies
  • 364 views
  • 2 likes
  • 6 in conversation