DATA Step, Macro, Functions and more

how to use array or proc sql to replace proc transpose to my below code

Reply
Regular Contributor
Posts: 214

how to use array or proc sql to replace proc transpose to my below code

[ Edited ]

Below is the code which i am using but i want to replace proc tranpose with either proc sql or arrays as i need to sort avalc with aval to sort as per aval.

	proc sort data=Anl1
	           out=anl2;
	   by subjid paramcd aval;
	run;

	data Id_adds (keep=subjid aval avalc paramcd seq);
	    set anl2;
	     by subjid paramcd aval;
	       seq+1;
	     if first.paramcd then seq=1;
	run;
	proc transpose data=Id_adds out=Id_trans(drop=_name_ _label_) ;
	     by subjid;
	     id paramcd seq;
	     var avalc;
	run;

any help?

Attachment
Respected Advisor
Posts: 4,797

Re: how to use array or proc sql to replace proc transpose to my below code

@vraj1

"..as i need to sort avalc with aval to sort as per aval..."

Maybe above will make sense to someone; it doesn't to me.

 

As for alternatives to Proc Transpose the following should be a quite good starting point:

http://www.sascommunity.org/wiki/A_Better_Way_to_Flip_(Transpose)_a_SAS_Dataset

 

 

Regular Contributor
Posts: 214

Re: how to use array or proc sql to replace proc transpose to my below code

i mean to say is sorting of avalc should be based on aval and i cannot use that in by statement in proc transpose

Respected Advisor
Posts: 4,797

Re: how to use array or proc sql to replace proc transpose to my below code

@vraj1

If you want help in the form of code then you need to provide sample data (a working SAS datastep creating such data) and then show us the desired result based on the sample data.

Regular Contributor
Posts: 214

Re: how to use array or proc sql to replace proc transpose to my below code

attached dataset. if you run my code it gives result but only thing i need is avalc in sorted order and if i include aval in by group in proc transpose it gives a wrong result

Attachment
Respected Advisor
Posts: 4,797

Re: how to use array or proc sql to replace proc transpose to my below code

@vraj1

Please provide a sample/show us how the desired output should look like. This might be very obvious to you but it's not to me and I don't want to spend time and effort on something which doesn't return what you're after.

Respected Advisor
Posts: 4,797

Re: how to use array or proc sql to replace proc transpose to my below code

[ Edited ]

@vraj1

Looking at the data and code you've now provided again: May be below gives you what you're after.

proc transpose data=Id_adds out=Id_trans(drop=_:);
  by subjid;
  id paramcd seq;
  var avalc;
run;

proc sql;
  create table want as
    select 
      idt.*,
      l.aval
    from 
      id_trans as idt
      left join
      (select subjid, aval, avalc from anl2) as l
      on idt.subjid=l.subjid
    order by l.aval, idt.subjid
    ;
quit;
Ask a Question
Discussion stats
  • 6 replies
  • 190 views
  • 0 likes
  • 2 in conversation