Restructuring data - might be a transpose or something else.

[BJeanW]

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

[Reeza]

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

---

[Ksharp]

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;

[BJeanW]

Thank you so much, both of you!  These worked perfectly. Much appreciated!!