DATA Step, Macro, Functions and more

Restructuring data - might be a transpose or something else.

Reply
New Contributor
Posts: 2

Restructuring data - might be a transpose or something else.

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        
IDADM_DATEDSCH_DATECODE_1TYPE_1CODE_2TYPE_2CODE_3TYPE_3
1June 12 2000July 6 2000xxxyyyfzz
1July 20 2000July 21 2000yyygzz  
1Dec 18 2000Jan 01 2001txxuzzjyy
2        
etc        
         
What  I want        
         
IDADM_DATEDSCH_DATECODETYPE    
1June 12 2000July 6 2000xxx    
1June 12 2000July 6 2000yyy    
1June 12 2000July 6 2000fzz    
1July 20 2000July 21 2000yyy    
1July 20 2000July 21 2000gzz    
1Dec 18 2000Jan 01 2001txx    
1Dec 18 2000Jan 01 2001uzz    
1Dec 18 2000Jan 01 2001jyy    
etc        
Super User
Posts: 23,357

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

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                

 

Super User
Posts: 10,700

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

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;
New Contributor
Posts: 2

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

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

Ask a Question
Discussion stats
  • 3 replies
  • 119 views
  • 0 likes
  • 3 in conversation