Proc SQL INTO with 0 rows of data

Accepted Solution Solved
Reply
Contributor
Posts: 29
Accepted Solution

Proc SQL INTO with 0 rows of data

Hello,

                i have simple proc sql statement to create macro variable name custid. I use this custid macro variable in other sql shown below to create table cust_dtl.

The problem arises when custid has 0 rows selected from cust_tbl which then gives error in sas log saying apparent symbolic reference not resolved. How do i replace value of custid with default value of 00 so it can be used in other proc sql without error.

 

proc sql noprint;
            select distinct cust_id format 15. into :custid SEPARATED BY ','
            from wk.cust_tbl
            ;
            quit;
                       

               %put &custid. ;

 

proc sql;

create table cust_dtl as

select name,dob from cust_det

where cust_id in (&custid.);

quit;

 


Accepted Solutions
Solution
‎07-12-2017 03:18 PM
Super User
Posts: 5,498

Re: Proc SQL INTO with 0 rows of data

[ Edited ]
Posted in reply to sasuser101

Do it the other way around.  Add this statement just before the first PROC SQL:

 

%let custid=00;

 

Then if PROC SQL selects no rows, it will leave CUSTID alone.

View solution in original post


All Replies
Solution
‎07-12-2017 03:18 PM
Super User
Posts: 5,498

Re: Proc SQL INTO with 0 rows of data

[ Edited ]
Posted in reply to sasuser101

Do it the other way around.  Add this statement just before the first PROC SQL:

 

%let custid=00;

 

Then if PROC SQL selects no rows, it will leave CUSTID alone.

Contributor
Posts: 29

Re: Proc SQL INTO with 0 rows of data

Posted in reply to Astounding
thank you for simple and effective solution.
☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 2 replies
  • 326 views
  • 5 likes
  • 2 in conversation