topic Re: Use the value of a variable in SAS Programming

Use the value of a variable

deleted_user — Tue, 15 Mar 2011 01:36:17 GMT

Hi !

I have a table which has one column , say order1. I want to use the value in order1 to actually order my sas dataset. Say if order1 = id, then i want to order my sas datset by id. How can we acheive this?

Thanks!!

Re: Use the value of a variable

Ksharp — Tue, 15 Mar 2011 06:59:29 GMT

Hi.
I think you maybe can use proc compare or data step's merge statement to get it.
And this maybe to code a macro.

Ksharp

Re: Use the value of a variable

deleted_user — Tue, 15 Mar 2011 15:18:45 GMT

Re: Use the value of a variable

deleted_user — Tue, 15 Mar 2011 15:24:27 GMT

This is my code where I first pull the data usin PROC SQL.
select c.* ......, order1, order2
from table1 a
join table 2 b
on a.id=b.id
join table3 c
on b.cd= c.cd

PROC SORT DATA=s_tmp OUT= s_sort nodupkey;
BY id , cd ;
RUN ;
And my data looks like this:
ID Code seq_no1 seq_no2 Order1 Order2
1 A 1 1 seq_no1 seq_no2
1 A 2 2 seq_no1 seq_no2
1 B 2 2 seq_no2 seq_no1
1 B 1 1 seq_no2 seq_no1

So now within an ID , I have different codes, so I want to first order by Code(eg A,B) and then Order by the values in column Order1 and Order2.
So Order1 has Seq_no1 for Code A, so within Code A , I would like to order by Seq_no1 and then by Seq_no2. However within code B, first I need ot order by Seq_no2 and then by seq_no1.

So a simple 'order by Order1' will not work here. Can u help me with using proc compare of a merge here?

Re: Use the value of a variable

Ksharp — Wed, 16 Mar 2011 03:32:20 GMT

Hi.
It spend some time to understand your post.
I am also not quit clarify.

If you want different order of ORDER1 and ORDER2 for different Code.

You can split your data into two datasets (each only contains A or B),then to order them
separately. at the end to concatenate these two datasets vertically.

Ksharp

Re: Use the value of a variable

deleted_user — Wed, 16 Mar 2011 14:03:20 GMT

My bad . Let me explain again.Changed the example to make it less confusing.
ID Code seq_no1 Order1 Order2 First Name LAst Name
1 A 1 seq_no1
1 A 2 seq_no1
1 B 2 FirstName Last name John Well
1 B 1 First NAme Last name Aaron Hancock

Since Order1=Seq_no1, all the observations with Code=A, should be ordered by seq_no_1.So this input data is alreayd sorted. Now since, Order1= First name , and Order2 = Last name, All the observations under Code=B should be first sorted on First names and then on last names. So irrespective of seq_no , all obs under cod=b should be sorted by NAmes.

If I use code as below it will not work .
Sel * from table...
order by Order1

I am trying to use a dynamic Order by clause But I am getting an error. Other than changing the data type, is there a way around this?
order by c.id, a.code,
case when &ord1_by = 'SEQ_NO' then SEQ_NO
when &ord1_by = 'PD' then PD
end ;
ERROR: Expression using equals (=) has components that are of different data types.
ERROR: Expression using equals (=) has components that are of different data types.
ERROR: Result of WHEN clause 2 is not the same data type as the preceding results.

Thanks! Message was edited by: sasnewbee

Re: Use the value of a variable

Ksharp — Thu, 17 Mar 2011 04:12:10 GMT

Hi.
You can make a new variable for order.
Such as :
if seq_no1 =seq_no1 then order=seq_no1;
else if seq_no2=seq_no2 then order=seq_no2
....................................

Ksharp

Re: Use the value of a variable

Cynthia_sas — Thu, 17 Mar 2011 16:29:16 GMT

Hi:
Without seeing more of your data, I am wondering whether you are misunderstanding the data fields. For example, it seems sort of odd to me that seq_no1 for Firstname=Aaron is 1 and seq_no1 for Firstname=John is 2. That makes me wonder whether all you have to do is:
[pre]
proc sort data=mydata out=sortdata;
by code seq_no1;
run;

OR

proc sort data=mydata out=sortdata;
by id code seq_no1;
run;
[/pre]

Perhaps the original data is telling you the order that the data was INTENDED to be in and if you sort by CODE and SEQ_NO1 or ID, CODE and SEQ_NO1, you will get the intended order. For the small data sample that you've posted, if you merely sorted by CODE and SEQ_NO1, your "B" codes would sort correctly.

cynthia