Need help with Transpose -

[deleted_user]

Hi support,

Would you please let me know how to do this in PROC step in TRANSPOSE ?

For example my data is like this :

20100120;var1;var2;ID;dob;relat
20100118;var1;var2;ID;dob;relat
20100218;var1;var2;ID;dob;relat
20100218;var1;var2;ID;dob;relat

Where var1 and var2 will be like question and answer.

And I want to write the record as :


20100120;ID;dob;relat;var1;var2;var1a;var2b;var2a;var2b;var3a;var3b;

assuming var1,var2, var3 are not similar. I am trying to output using PROC TRANSPOSE and for somereason I am getting error message saying - ID value occurs twice.

Do I need to sort data and delete duplicate by merge variable ? I just used PROC SORT and didn't used nodupkey word. Please let me know.

thanks,
sasbase9
latha

[sbb]

Curious if you attempted using PROC TRANSPOSE, after reading the DOC, and considering the examples that are available at the SAS support http://support.sas.com/ either using the SEARCH facility at the site or with an Internet search?

Also, you show 1 row for 20100120, then multiple rows for another date-related variable -- yet you attempt to describe the desired result which doesn't necessarily jive with the "input source".

Scott Barry
SBBWorks, Inc.

Recommended Google search, this topic/post:

proc transpose how to site:sas.com

[deleted_user]

Yes, That's correct. I have data for HRA and we get data by Completion date and by each of the question code followed by an answer in one record. I wanted to combine all of the unique questions related to that person into one record for all possible questions and I am not sure if PROC TRANSPOSE will do that.

thanks,

[sbb]

Sure it can - you may need/want to summarize or GROUP your data first, before invoking TRANSPOSE.

Scott Barry
SBBWorks, Inc.

[deleted_user]

Any example code that can site with this scenario? I never worked on PROC TRANSPOSE or grouping data like this before.

thanks for help,
sasbase9

[sbb]

As suggested, the SAS support http://support.sas.com/ website and its SEARCH facility can reveal much information for PROC TRANSPOSE - both SAS-hosted documentation and also supplemental conference/technical papers on the topic.

Also, the forum archives will provide useful reference information as well, when searched.

Scott Barry
SBBWorks, Inc. Message was edited by: sbb

[ChrisNZ]

Agreed with Scott: try the various possibilities offered by the procedure, and if you end up being stumped after trying your best, come back with some code.

[deleted_user]

hi Chris,

I tried to output using PROC TRANSPOSE and for some reason my answers for question codes are not coming out.

Here is the code I used -

proc transpose data=bcmaraw_out out=hra_rollup /*prefix= Q let */ ;
by mrgvar QUSTNCD_SHRT ;
id QUSTNCD_SHRT;
var HA_QUESTION_TEXT;
run;

- where mergvar is combination of memberID + MemDOB + RELAT + HRA DATE OF COMP .

All of my "QUSTNCD_SHRT" came out as variables in output, but corresponding answer is not outputted under question. "HA_QUESTION_TEXT" is the variable coming on data as an answer to the question.

thanks,

[sbb]

Strongly suggest that you start with some instream DATALINES, or consider SASHELP.CLASS, so you can get familiar with the PROC TRANSPOSE processing.

I very much expect that you can emulate your output rqmts to-start with using SASHELP.CLASS, get the processing well-defined, then move forward (slowly) with introducing your specific data.

You need to learn the procedure/process first, then adapt the process(ing) to your specific input data conditions and output data requirements -- one step at a time.

And, remember, you know your data - we don't - so to reveal SAS code in your post/reply for which we have no clue how you mangled the input side is less-than-useful if you actually expect to get assistance from other subscribers (without making it a guessing contest).

Scott Barry
SBBWorks, Inc.