Hi - I suspect this is a really easy fix (I'm a bit of a rookie SAS user - version 11 at work I think). Here is what I am trying to do with my data (fake example below). I tried a proc transpose of both the code and the type, by the ID, but it didn't give me what I want because my original file contains >1 row per id (at least I think that's why. At any rate what I get is still more than one column per code, and I need them to be all in one column.) And I want to not include any rows where a code is missing.
I also tried an array:
array code1 code_1-code_3;
array type1 type_1-type_3;
do I=1 to 3 until (code1='');
end;
But I got this error: ERROR: Mixing of implicit and explicit array subscripting is not allowed.
Any help is much appreciated!
Thanks so much,
Jean
What I have | ||||||||
ID | ADM_DATE | DSCH_DATE | CODE_1 | TYPE_1 | CODE_2 | TYPE_2 | CODE_3 | TYPE_3 |
1 | June 12 2000 | July 6 2000 | x | xx | y | yy | f | zz |
1 | July 20 2000 | July 21 2000 | y | yy | g | zz | ||
1 | Dec 18 2000 | Jan 01 2001 | t | xx | u | zz | j | yy |
2 | ||||||||
etc | ||||||||
What I want | ||||||||
ID | ADM_DATE | DSCH_DATE | CODE | TYPE | ||||
1 | June 12 2000 | July 6 2000 | x | xx | ||||
1 | June 12 2000 | July 6 2000 | y | yy | ||||
1 | June 12 2000 | July 6 2000 | f | zz | ||||
1 | July 20 2000 | July 21 2000 | y | yy | ||||
1 | July 20 2000 | July 21 2000 | g | zz | ||||
1 | Dec 18 2000 | Jan 01 2001 | t | xx | ||||
1 | Dec 18 2000 | Jan 01 2001 | u | zz | ||||
1 | Dec 18 2000 | Jan 01 2001 | j | yy | ||||
etc |
the array method is what you want, and you're pretty close.
array _code(3) $ code_1-code_3; *These look like character variables, you may need to specify a length;
array _type(3) $ type_1-type_3;
do I=1 to 3;
if not missing(_code(i)) then do;
Code_Value = _code(i);
Code_Type = _type(i);
output;
end;
drop code_1-code_3 type_1-type_3;
@BJeanW wrote:
Hi - I suspect this is a really easy fix (I'm a bit of a rookie SAS user - version 11 at work I think). Here is what I am trying to do with my data (fake example below). I tried a proc transpose of both the code and the type, by the ID, but it didn't give me what I want because my original file contains >1 row per id (at least I think that's why. At any rate what I get is still more than one column per code, and I need them to be all in one column.) And I want to not include any rows where a code is missing.
I also tried an array:
array code1 code_1-code_3;
array type1 type_1-type_3;
do I=1 to 3 until (code1='');
end;
But I got this error: ERROR: Mixing of implicit and explicit array subscripting is not allowed.
Any help is much appreciated!
Thanks so much,
Jean
What I have ID ADM_DATE DSCH_DATE CODE_1 TYPE_1 CODE_2 TYPE_2 CODE_3 TYPE_3 1 June 12 2000 July 6 2000 x xx y yy f zz 1 July 20 2000 July 21 2000 y yy g zz 1 Dec 18 2000 Jan 01 2001 t xx u zz j yy 2 etc What I want ID ADM_DATE DSCH_DATE CODE TYPE 1 June 12 2000 July 6 2000 x xx 1 June 12 2000 July 6 2000 y yy 1 June 12 2000 July 6 2000 f zz 1 July 20 2000 July 21 2000 y yy 1 July 20 2000 July 21 2000 g zz 1 Dec 18 2000 Jan 01 2001 t xx 1 Dec 18 2000 Jan 01 2001 u zz 1 Dec 18 2000 Jan 01 2001 j yy etc
Double PROC TRANSPOSE ,
data have;
infile cards expandtabs truncover;
input ID ADM_DATE : $20. DSCH_DATE : $20. (CODE_1 TYPE_1 CODE_2 TYPE_2 CODE_3 TYPE_3) ($);
cards;
1 June122000 July62000 x xx y yy f zz
1 July202000 July212000 y yy g zz
1 Dec182000 Jan012001 t xx u zz j yy
;
run;
proc transpose data=have out=temp;
by id adm_date dsch_date notsorted;
var CODE_1 TYPE_1 CODE_2 TYPE_2 CODE_3 TYPE_3;
run;
data temp;
set temp;
name1=scan(_name_,1,'_');
name2=scan(_name_,2,'_');
run;
proc transpose data=temp out=want(drop=name2 _name_);
by id adm_date dsch_date name2 notsorted;
var col1;
id name1;
run;
Thank you so much, both of you! These worked perfectly. Much appreciated!!
Available on demand!
Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.
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.