BookmarkSubscribeRSS Feed
CamRutherford
Fluorite | Level 6

Hello,

 

I have the below code which is in a data step but i wanted to know if this was possible to do in a Proc SQL statement?

DATA SCORE;
SET WEEKS.TEST;
BY SBRID;
COUNTER+1;
IF FIRST.SBRID THEN COUNTER=1;
RUN;

Thanks

 

1 REPLY 1
RW9
Diamond | Level 26 RW9
Diamond | Level 26

Well, it is possible.  What you have to remember is that SQL is different from SAS, SAS treats each obsevation step by by step, and if sorted then you can do this calculation.  SQL treats observations as a bunch of observations - order is only given by logical sort, not by logical order in a data table.  So you can use the monotonic() function like this:

proc sql;
  create table SCORE as
  select  SBRID,
             monotonic() as COUNTER
  from    WEEKS.TEST;
quit;

However, if you have the data:

SBRID

001

001

001

 

There is no logical way of telling SQL that the order is:

SBRID  COUNTER

001       1

001       2

001       3

 

Rather than:

SBRID  ORDER

001      3

001       1

001       2

 

Or any other combination.  Is there a reason why you need to do this in SQL, generally speaking for SQL an observation count in itself is not really useful, aggregate functions are of more use (sum, count etc.).

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
  • 1 reply
  • 3437 views
  • 0 likes
  • 2 in conversation