Problems checking dataset if observations exist.

[ncsthbell]

I am using the following to read a work dataset that was created right before the data_null step to check to see if any observations exists. When I added the code to my program it turns the next CREATE TABLE' statement red and gives errors.

I cannot figure out what have have wrong with my data step.  Any help would be appreciated.

data _null_;
 call symput('AnyObs','0'); /* set to 0 for No */
 set Work.Finacial_CORP(obs=1);
 call symput('AnyObs','1'); /* if got here there are obs so set to 1 for Yes */
run;

CREATE TABLE work.Financial_FRAN AS
   SELECT DISTINCT
          t1.DISTRICT, 
          t1.ACCOUNT_TYPE,
          t1.ACCOUNT_BEG_DATE, 
          t1.ACCOUNT_END_DATE, 
          t1.COUNTY_NAME, 
          t1.ADDR_LINE_1, 
	     t1.ADDR_LINE_2,
          t1.CITY, 
          t1.STATE, 
          t1.ZIP, 
	      count(*) FORMAT=comma7. AS FRANRptCnt
      FROM WORK.DATA_SELECTED t1
      WHERE t1.ACCOUNT_TYPE = 999;

[Reeza]

Where did your PROC SQL go?

You need a PROC SQL; before your CREATE TABLE statement and a QUIT; at the end.

PROC SQL;
CREATE TABLE work.Financial_FRAN AS
   SELECT DISTINCT
          t1.DISTRICT, 
          t1.ACCOUNT_TYPE,
          t1.ACCOUNT_BEG_DATE, 
          t1.ACCOUNT_END_DATE, 
          t1.COUNTY_NAME, 
          t1.ADDR_LINE_1, 
	     t1.ADDR_LINE_2,
          t1.CITY, 
          t1.STATE, 
          t1.ZIP, 
	      count(*) FORMAT=comma7. AS FRANRptCnt
      FROM WORK.DATA_SELECTED t1
      WHERE t1.ACCOUNT_TYPE = 999;
QUIT;

[ncsthbell]

In creating multiple tables and I put the 'PROC SQL'  statement at the beginning of creating all the tables and the 'QUIT' at the end.  I changed it to put the PROC SQL and QUIT statements around each CREATE TABLE statement and that worked, however I got an error

stating that my work dataset does not exist. In the log it shows that it was created.    I do notice that it seems to be looking for the same dataset name but with a '.DATA' on the end.  Here is the message....

174                  t1.CORP_CD401S_TC22,
175        	      count(*) FORMAT=comma7. AS CORPRptCnt
176              FROM WORK.DATA_SELECTED t1
177              WHERE t1.ACCOUNT_TYPE = 250;
NOTE: The query requires remerging summary statistics back with the original data.
NOTE: Table WORK.FINANCIAL_CORP created, with 3 rows and 34 columns.

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

179        
180        /*Check if data set has data and set variable as a flag to*/
181        /*  trigger the CORP report(tab) to be created. */
182        data _null_;
183         call symput('AnyObs','0'); /* set to 0 for No */
184         set Work.Finacial_CORP(obs=1);
ERROR: File WORK.FINACIAL_CORP.DATA does not exist.
185         call symput('AnyObs','1'); /* if got here there are obs so set to 1 for Yes */
186        run;

[Reeza]

typo....

NOTE: Table WORK.FINANCIAL_CORP created, with 3 rows and 34 columns.

Missing an N here:

ERROR: File WORK.FINACIAL_CORP.DATA does not exist.

[Reeza]

typo....

NOTE: Table WORK.FINANCIAL_CORP created, with 3 rows and 34 columns.

Missing an N here:

ERROR: File WORK.FINACIAL_CORP.DATA does not exist.

[ncsthbell]

I am SO sorry for not seeing this!  Just kinda of overwhelmed right now with so many things on my plate. I'm sure you know how that feels.

Thanks so much for your assistance.

[ncsthbell]

I am still not getting the results that I want in the step to check for observations in the dataset.   The data set is created with 3 rows, however, after running the data code to check the obervastions in the dataset, it returns the value of '0' which indicates no rows.

I have never used this before and don't know what I have wrong in the data step.  All I need to know is if there is data or not and the '0' means no data.  Is this the best way to check? The Work.Financial_CORP datasest shows it has 3 rows. I used a put statement to display the value of CORPobs and it is showing value of 0. 

Here is the displayed results from log:

NOTE: The query requires remerging summary statistics back with the original data.
NOTE: Table WORK.FINANCIAL_CORP created, with 3 rows and 35 columns.

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

181        
182        /*Check if data set has data and set variable as a flag to*/
183        /*  trigger the CORP report(tab) to be created. */
184        data _null_;
185         call symput('CorpObs','0'); /* set to 0 for No */
186         set Work.Financial_CORP(obs=1);
187         call symput('CorpObs','1'); /* if got here there are obs so set to 1 for Yes */
188        run;

NOTE: There were 1 observations read from the data set WORK.FINANCIAL_CORP.
NOTE: DATA statement used (Total process time):
      real time           0.00 seconds
      cpu time            0.00 seconds
      

189        %put &CorpObs;
0

[Reeza]

There's a lot of papers written on this, no need to reinvent the wheel. I am curious as to why the macro variable does resolve to 0 though.

At any rate, after thinking about this, I don't think this is a good method, because you'll get an error in your log if it's 0 and that's not a good way to test something.

Google on Lexjansen.com -> Check if sas dataset is empty

http://analytics.ncsu.edu/sesug/2011/CC19.Childress.pdf

[ballardw]

Save some headaches: When you run Proc Sql to create a table SAS sets the value of an automatic variable &sqlobs to the number of records returned.

So IMMEDIATELY after the SQL step add something like

%let CorpObs = %eval(&sqlobs > 0);

to get a 1 / 0 value for CorpObs.