topic Re: query in SAS Procedures

query

PriyaSaha — Mon, 08 Dec 2014 08:48:32 GMT

I have a input dataset like below

input dataset
Name
A
B
E
F
D
C

Need a datase like below

output dataset
Name
C
D
F
E
B
A

Please help me write this query.

Thanks in advance priya

Re: query

RW9 — Mon, 08 Dec 2014 09:28:52 GMT

Hi,

I am afraid you have no option other than to put the values in yourself. There appears to be no logical reason why A is last and C is first for instance, so you cannot write a logical step to do this.

So, putting the information in yourself, you have several options. Datastep, if statements, user formats. Me, I would go with an array:

data want;

set have;

array letters{26} ("C","D","F",...); /* In the order you want */

do I=1 to 26;

if letters{I}=name then var_id=I;

end;

run;

Then sort by var_id.

Re: query

gsreddy — Mon, 08 Dec 2014 09:46:52 GMT

step1:craete a new variable , a=_N_

step2: sort the above by variable a DESC;

Re: query

RW9 — Mon, 08 Dec 2014 09:50:23 GMT

Unfortunately its not descending though, F - E - B? It just looks random. Unless there is further information - such as the data was sorted that way prior, then you could use _n_ and then re-sort back. However that's not stated in the post. And the question would be, why? What value is there having some arbitrary sort order without logic. If the data changes the order might change and then you would not be able to re-create it.

Re: query

Patrick — Mon, 08 Dec 2014 10:39:41 GMT

If this is just about getting your source data set in reversed order without having a variable for sorting then below code could do.

data have;

input Name $;

datalines;

;

run;

data want;

do i=nobs to 1 by -1;

set have nobs=nobs point=i;

output;

end;

stop;

run;

Re: query

Patrick — Mon, 08 Dec 2014 10:44:29 GMT

I believe what proposes is to create a variable from the source data set which holds the original Obs number - and then to sort the data set using this obs number variable in descending order. This should work and create the original source data set in reversed.

Re: query

CTorres — Mon, 08 Dec 2014 15:12:45 GMT

I agree with Patrick. Gsreddy solution would be something like this:

data temp / view=temp;

set sashelp.class;

a=_N_;

run;

proc sort data=temp out=class(drop=a);

by descending a;

run;

Ctorres