BookmarkSubscribeRSS Feed
vraj1
Quartz | Level 8

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?

6 REPLIES 6
Patrick
Opal | Level 21

@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

 

 

vraj1
Quartz | Level 8

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

Patrick
Opal | Level 21

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

vraj1
Quartz | Level 8

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

Patrick
Opal | Level 21

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

Patrick
Opal | Level 21

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

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 6 replies
  • 903 views
  • 0 likes
  • 2 in conversation