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;
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;
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;
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.
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.
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.
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
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
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.
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 16. Read more here about why you should contribute and what is in it for you!
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.
Select SAS Training centers are offering in-person courses. View upcoming courses for: