Transposing Data.

[sk1]

I have a dataset that has Id, Quest(these are the question no.) and some text for each of the question. I would like to transpose data so that certain combination of question and text would be per case.


Instead of explaining I will just give input and the desire output.

Input Dataset:
ID Quest Text
1111 C2_1 abcd
1111 C2_2 eeee
1111 C2_3 ffff
1111 C3_1 gggg
1111 C3_2 xyzh
1111 C3_3 mmmm
1111 D4_1 efgh
1111 D4_2 lmno
1111 D4_3 kkkk
1111 D5_1 txyz
1111 D5_2 klmn
1111 D5_3 ctyx


Desired Output should be:

ID QuestInd TextInd QuestOcc TextOcc
1111 C3_1 gggg C2_1 abcd
1111 C3_2 xyzh C2_2 eeee
1111 C3_3 mmmm C2_3 ffff
1111 D5_1 txyz D4_1 efgh
1111 D5_2 klmn D4_2 lmno
1111 D5_3 ctyx D4_3 kkkk

I tried using retain, .first, creating separte data set and remerging.. etc. etc.. nothing seems to be working -
any help will be appriciated.
thanks SK.

 

[jymm]

Almost looks like the matching key is the whole ID with the first char and the last char of the Quest??? Right???

Build a new 'key' as you read in the records then use first & last to build the output string. I do not have SAS anymore so I can not test this, but it should be something like this:

Data Questions;
Set <inputfile>;
Key = Trim(ID) || SubStr(Quest,1,2) || SubStr(Quest,4,1);
Occ = Substring(Quest,2,1);

Proc Sort Data=Questions;
by Key Descending Occ;

Data AllInfo;
Format QuestInd $char50.; /* ymmv */
Format TextInd $char50.;
Format QuestOcc $Char50.;
Format TextOcc $Char50.;
Retain QuestInd;
Retain TextInd;
Retain QuestOcc;
Retain TextOcc;
Set Questions;
By Key;
/* since I do not know if there are more than two matches I do not know what the condition here is, but it should get u started */
If Occ = '3' or Occ = '5'
Then Do;
QuestInd = Quest;
TextInd = Text;
End;
Else Do; /* I am assuming it is either 2 or 4 at this point from your example */
QuestOcc = Quest;
TextOcc = Text;
End;
If Last.Key Output;

ymmv but this should get you close...

 

[jymm]

you might also want to blank out the retain fields on the first.key just to keep it all straight

 

[sk1]

Thanks jymm,
This was very helpful.. I am almost there!
only thing is that it splits the record in two.

So this is what I am getting
questind questocc textOcc textInd
C2_1 abcd
C3_1 gggg


Any thoughts?

 

[jymm]

Post the code that you finally ended up with and I bet we can figure it out --- make sure you have the If Last.key then output statement since that should only put out one record per key (so multiple records per key should give one output record)...

 

[sk1]

You are right it gives one record per key but what I want as output is not one record per newly generated key, what I am looking for is one record per id, questInd QuestOcc combination.
here is the code that I used-

Data Questions;
Set &outfile;
Key = Trim(ID) || SubStr(Quest,1,2) || SubStr(Quest,4,1);
Occ = Substr(Quest,2,1);
run;
Proc Sort Data=Questions;
by Key Descending Occ;
run;

Data AllInfo;
Length QuestInd QuestOcc $10. TextInd TextOcc $1500.;
Retain QuestInd TextInd QuestOcc TextOcc;
Set Questions;
By Key;
if first.key then
do; QuestInd=" "; TextInd=" "; QuestOcc=" "; TextOcc=" ";end;

/* since I do not know if there are more than two matches I do not know what the condition here is, but it should get u started */
If Occ = '3' or Occ = '5'
Then Do;
QuestInd = Quest;
TextInd = Text;
End;
Else Do; /* I am assuming it is either 2 or 4 at this point from your example */
QuestOcc = Quest;
TextOcc = Text;
End;
If Last.Key then Output;
run;

/*
BELOW IS AN ALTERNATE SOLUTION I CAME UP WITH BUT IT IS NOT SO ELEGANT BUT IT WORKS .
*/
/**FILE FOR INDUSTRY AND OCCUPATION CODING**/
data IO_1(keep=id quest nquest text) IO_2(keep=id quest nquest text);
set &outfile.;
by id quest;
quest=upcase(quest);
if quest in("C2_1" "C2_2" "C2_3" "C2_4" "C2_5"
"C2_6" "C2_7" "C2_8" "C2_9" "C2_10"
"C2_11" "C2_12" "C2_13" "C2_14" "C2_15"
"C3_1" "C3_2" "C3_3" "C3_4" "C3_5"
"C3_6" "C3_7" "C3_8" "C3_9" "C3_10"
"C3_11" "C3_12" "C3_13" "C3_14" "C3_15"
"D4_1" "D4_2" "D4_3" "D4_4" "D4_5"
"D4_6" "D4_7" "D4_8" "D4_9" "D4_10"
"D4_11" "D4_12" "D4_13" "D4_14" "D4_15"
"D5_1" "D5_2" "D5_3" "D5_4" "D5_5"
"D5_6" "D5_7" "D5_8" "D5_9" "D5_10"
"D5_11" "D5_12" "D5_13" "D5_14" "D5_15"
);
/*Create New variable nQuest and assign the value to nQuest based on the Ind and Occ combination.
*/
length nquest $10.;

if quest in("C2_1" "C2_2" "C2_3" "C2_4" "C2_5"
"C2_6" "C2_7" "C2_8" "C2_9" "C2_10"
"C2_11" "C2_12" "C2_13" "C2_14" "C2_15"
"D4_1" "D4_2" "D4_3" "D4_4" "D4_5"
"D4_6" "D4_7" "D4_8" "D4_9" "D4_10"
"D4_11" "D4_12" "D4_13" "D4_14" "D4_15"
) then do; nquest=quest; output IO_1;end;

if quest in("C3_1" "C3_2" "C3_3" "C3_4" "C3_5"
"D5_1" "D5_2" "D5_3" "D5_4" "D5_5") then do;
if quest="C3_1" then nquest="C2_1";
if quest="C3_2" then nquest="C2_2";
if quest="C3_3" then nquest="C2_3";
if quest="C3_4" then nquest="C2_4";
if quest="C3_5" then nquest="C2_5";
if quest="C3_6" then nquest= "C2_6" ;
if quest="C3_7" then nquest= "C2_7" ;
if quest="C3_8" then nquest= "C2_8" ;
if quest="C3_9" then nquest= "C2_9" ;
if quest="C3_10" then nquest="C2_10";
if quest="C3_11" then nquest="C2_11";
if quest="C3_12" then nquest="C2_12";
if quest="C3_13" then nquest="C2_13";
if quest="C3_14" then nquest="C2_14";
if quest="C3_15" then nquest="C2_15";
if quest="D5_1" then nquest="D4_1";
if quest="D5_2" then nquest="D4_2";
if quest="D5_3" then nquest="D4_3";
if quest="D5_4" then nquest="D4_4";
if quest="D5_5" then nquest="D4_5";
if quest="D5_6" then nquest= "D4_6" ;
if quest="D5_7" then nquest= "D4_7" ;
if quest="D5_8" then nquest= "D4_8" ;
if quest="D5_9" then nquest= "D4_9" ;
if quest="D5_10" then nquest="D4_10";
if quest="D5_11" then nquest="D4_11";
if quest="D5_12" then nquest="D4_12";
if quest="D5_13" then nquest="D4_13";
if quest="D5_14" then nquest="D4_14";
if quest="D5_15" then nquest="D4_15";
output IO_2;
end;
run;


proc sort data=IO_1(rename=(quest=questOcc text=textOcc));by id nquest;
proc sort data=IO_2(rename=(quest=questInd text=textInd));by id nquest;

data dsn.SpcDumpIO_05202004(drop=nquest);
merge IO_1
IO_2
;by id nquest;
run;
thanks for taking time out in helping me.
-