BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
Terho
Obsidian | Level 7

Hello,

 

I am looking to create the following logic:

 

1. Pull data from database

2. If the dataset is not empty, then run a data step to create a final dataset.

 

In the flow below, curpd_dataset is the data from database. however I cannot figure out a way to get the syntax correct:

 

data _NULL_;

if 0 then set curpd_dataset nobs=num_obs;

if num_obs = 0 then call execute ('%put Using Existing Data;');

if num_obs > 0 then 'DO DATA STEP HERE';

                   data libname.FINAL_DATASET;

                   set curpd_dataset ;

stop;

run;

 

Anyone have any tips on a way to make this work? I would eventually want to integrate this into a Macro.

 

Thanks

 

 

1 ACCEPTED SOLUTION

Accepted Solutions
Astounding
PROC Star

Here's the approach I would use.  You have already identified CALL EXECUTE as a viable tool here.

 

data _null_;

if done then put 'There were no observations.  Use the existing data.';

else call execute('data libname.final_dataset; set curpd_dataset; run;');

stop;

set curpd_dataset end=done;

run;

 

I'm not sure if you intended more code to go into that final DATA step, but you could always add more.  As you mentioned, if the DATA step is actually more complex, you might want to convert the code to a macro instead.  But so far, there isn't a need to do that.

View solution in original post

7 REPLIES 7
LinusH
Tourmaline | Level 20
Try to use proc contents or data step functions for SAS data sets or dictionary.tables from PROC SQL or sashelp.vstable.vstable.
Data never sleeps
BrunoMueller
SAS Super FREQ

Hi

 

Please note, that the NOBS= option of the SET statement can only be used with SAS data sets. If your data is coming from a DBMS like Oracle, DB2 etc, then this approach can not be used. Instead you have to count the number of rows.

 

To run code contionally, one usually uses the SAS Macro Language.

 

Find below an example that shows how to count the rows, and then run some DATA Step.

 


%macro createData(dsn=);
  %local rowCount;

  proc sql noprint;
    select
      count(*)
    into  
      :rowCount trimmed
    from
      &dsn
    ;
  quit;

  %put NOTE: &=rowCount;

  %if &rowCount > 0 %then %do;

    data newTable;
      set &dsn;
    run;

  %end;
  %else %do;
    %put NOTE: &sysmacroname &dsn is empty;
  %end;
%mend;

%createData(dsn=xora.tempcars)

Bruno

jklaverstijn
Rhodochrosite | Level 12

Bruno,
Be very careful to count the number of rows in a table that is not acessed by the base engine (eg. a RDBMS). You may hit a full table scan and if there are gazillions of rows you will regret the approach. Trying to read one row and determining success or failure is usually better in that case.

Regards,
- Jan.

BrunoMueller
SAS Super FREQ

Hi Jan

 

Thanks for pointing this out. Some SAS/ACCESS to Interfaces will pass down the OBS= data set option as part of the generated SQL Query. There might be various ways to optimize this, depending on the DBMS used.

 

Bruno

jklaverstijn
Rhodochrosite | Level 12
Hi Bruno,

You're welcome. Problem with many (R)DBMS's is that the row count is not Always accurate and up-to-date. Oracle for instance keeps a count in the table statistics but these are updated explicitly and not automatically. So the count may be off. Even considerably. Or not be available in the first place. I must assume that the performance penalty in transactional systems is prohibitive. As long-time SAS users we have become accustomed to NOBS and even NLOBS, but it is a luxury that other vendors not always provide.
Astounding
PROC Star

Here's the approach I would use.  You have already identified CALL EXECUTE as a viable tool here.

 

data _null_;

if done then put 'There were no observations.  Use the existing data.';

else call execute('data libname.final_dataset; set curpd_dataset; run;');

stop;

set curpd_dataset end=done;

run;

 

I'm not sure if you intended more code to go into that final DATA step, but you could always add more.  As you mentioned, if the DATA step is actually more complex, you might want to convert the code to a macro instead.  But so far, there isn't a need to do that.

Terho
Obsidian | Level 7

Thanks all for the replies! I don't do too much in the final data step, so this will fit my needs for now!

 

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
  • 7 replies
  • 9466 views
  • 4 likes
  • 5 in conversation