topic Re: Restructuring data - might be a transpose or something else. in SAS Programming

Restructuring data - might be a transpose or something else.

BJeanW — Thu, 22 Feb 2018 01:07:07 GMT

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

Re: Restructuring data - might be a transpose or something else.

Reeza — Thu, 22 Feb 2018 01:24:10 GMT

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

Re: Restructuring data - might be a transpose or something else.

Ksharp — Thu, 22 Feb 2018 04:53:25 GMT

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;

Re: Restructuring data - might be a transpose or something else.

BJeanW — Thu, 22 Feb 2018 14:00:27 GMT

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