I have a SAS coding issue which I am hoping someone can assist with.
My job is using the following instruction to create a SAS macro variable called &init_valueAMPEPCNT : -
proc sql;
select TOTAL_CR_CNT into :init_valueAMPEPCNT from perm.recordsAMPEP3;
where file_id = 'EFTAMPNZ';
quit;
Problem is, the job returns a condition code of RC=4.
This is what is returned: -
149 proc sql;
150 select TOTAL_CR_CNT into :init_valueAMPEPCNT from perm.recordsAMPEP3;
151 where file_id = 'EFTAMPNZ';
WARNING: This SAS global statement is not supported in PROC SQL. It has been ignored.
152 quit;
I am wanting a clean run of RC=0.
Referring to documentation on the internet I think our version of SQL may not support this representation (with the colon :🙂 to signify a macro variable).
I'm wondering if there is an alternative way of coding this short of not using SQL at all and going with SAS code only.
Removed the semi-colon and it worked!!! Thanks very much for a quick response.
remove the semicolon before the where clause.
Oops sorry sir @PGStats for the duplication. I didn;t see your post.
Are you sure whether your proc sql syntax is correct, i see one error
select TOTAL_CR_CNT into :init_valueAMPEPCNT from perm.recordsAMPEP3; /*shouldn't be semicolon here*/
The syntax should ideally be
proc sql;
select TOTAL_CR_CNT into :init_valueAMPEPCNT from perm.recordsAMPEP3
where file_id = 'EFTAMPNZ';
quit;
Removed the semi-colon and it worked!!! Thanks very much for a quick response.
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 25. 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.