BookmarkSubscribeRSS Feed
☑ This topic is solved. Need further help from the community? Please sign in and ask a new question.
Batman
Quartz | Level 8

I think the sql procedure should return the same number of observations the data step below, but it doesn't.   Does anyone know why?

 

28 proc sql;
29 create table check1 as select *
30 from dat.PECOS_dat(firstobs=2)
31 where profit_status3 ne '';
NOTE: Table WORK.CHECK1 created, with 1 rows and 19 columns.

32 quit;
NOTE: PROCEDURE SQL used (Total process time):
real time 0.03 seconds
cpu time 0.01 seconds

33
34 data check2;
35 set dat.PECOS_dat(firstobs=2);
36 if profit_Status3 ne '';
37 run;

NOTE: There were 7416 observations read from the data set DAT.PECOS_DAT.
NOTE: The data set WORK.CHECK2 has 2 observations and 19 variables.
NOTE: DATA statement used (Total process time):
real time 0.02 seconds
cpu time 0.01 seconds

1 ACCEPTED SOLUTION

Accepted Solutions
FreelanceReinh
Jade | Level 19

Hello @Batman,

 

It's not the difference between PROC SQL and DATA step, but the difference between WHERE and IF: The WHERE filter (be it in a PROC SQL or a DATA step) is applied before the firstobs= dataset option, whereas the subsetting IF is applied after the dataset option. So, in the first case firstobs=2 removes one of the observations satisfying the WHERE condition profit_status3 ne '' from the result, unless there is none at all. The observation skipped with firstobs=2 in the second case might not satisfy the IF condition anyway.

 

Example:

data have;
input x profit_status3 $;
cards;
1 .
2 b
3 c
;

 

 

View solution in original post

2 REPLIES 2
FreelanceReinh
Jade | Level 19

Hello @Batman,

 

It's not the difference between PROC SQL and DATA step, but the difference between WHERE and IF: The WHERE filter (be it in a PROC SQL or a DATA step) is applied before the firstobs= dataset option, whereas the subsetting IF is applied after the dataset option. So, in the first case firstobs=2 removes one of the observations satisfying the WHERE condition profit_status3 ne '' from the result, unless there is none at all. The observation skipped with firstobs=2 in the second case might not satisfy the IF condition anyway.

 

Example:

data have;
input x profit_status3 $;
cards;
1 .
2 b
3 c
;

 

 

sas-innovate-2024.png

Available on demand!

Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.

 

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
  • 2 replies
  • 240 views
  • 0 likes
  • 3 in conversation