topic how to use array or proc sql to replace proc transpose to my below code in SAS Programming

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

vraj1 — Wed, 11 Oct 2017 11:39:30 GMT

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?

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

Patrick — Wed, 11 Oct 2017 11:49:03 GMT

@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

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

vraj1 — Wed, 11 Oct 2017 11:51:52 GMT

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

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

Patrick — Wed, 11 Oct 2017 11:55:21 GMT

@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.

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

vraj1 — Wed, 11 Oct 2017 12:09:02 GMT

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

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

Patrick — Wed, 11 Oct 2017 12:12:12 GMT

@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.

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

Patrick — Wed, 11 Oct 2017 12:39:52 GMT

@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;