BookmarkSubscribeRSS Feed
alilacey0
Fluorite | Level 6

I have a dataset similar to 

data NATR332;
input Y1 Y2;
datalines;
146 141
141 143
135 139
142 139
140 140
143 141
138 138
137 140
142 142
136 138
run;

 

I used proc sql to find the difference between Y1 and Y2 and removed the rows where the difference is = 0 by using the code

 

proc SQL;
/*create table temp as*/
select *,
Y1 - Y2 as Difference,
from NATR332
where (Y1-Y2 ^= 0)
;

 

I now want to create a new column called rank where I rank the absolute value of the differences. I tried to use the rank () over partition in proc sql and didn't have any luck so I was thinking I would maybe have to use the proc rank function. How would I go about creating this column?

 

Thank you in advance.

4 REPLIES 4
pau13rown
Lapis Lazuli | Level 10

but you seem to know the answer ie use proc rank on the abs value of the difference ie abs(difference). And use "out=" on the proc rank statement to get the dataset. Incidentally, I wouldn't see any reason to exclude the 0's.

Reeza
Super User

I tried to use the rank () over partition in proc sql

 

PROC SQL doesn't support partition. PROC RANK will work, and the documentation has examples which is always the place to start.

A fully worked example is here:

http://documentation.sas.com/?cdcId=pgmmvacdc&cdcVersion=9.4&docsetId=proc&docsetTarget=p12aek9f6xhl...

ChrisNZ
Tourmaline | Level 20

It's a good habit to always think about numerical precision (and missing values).

 

Something like

 

 where abs(Y1-Y2) > 1e-9

 

is better than 

 

 where (Y1-Y2 ^= 0)

ChrisNZ
Tourmaline | Level 20

[ Erroneous post removed by jet-lagged author 🙂 ]

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
  • 4 replies
  • 810 views
  • 2 likes
  • 4 in conversation