topic Re: into statement in proc sql-get error in SAS Programming

into statement in proc sql-get error

Ronein — Wed, 06 Mar 2019 07:05:47 GMT

Hello
I am using into statement in proc sql.
In this example I get an error
"
WARNING: Apparent symbolic reference E not resolved.
9057 &E&N., :D1 - :&D&M.
."
What is wrong here please?

Data RawData;
Input EMPID $3. Ddate $5. Y 9.;
cards;
P01 1801 258766
P01 1802 92139678
P01 1803 921396
P01 1804 898755
P02 1801 566511
P02 1802 464467896
P02 1803 87932
P02 1804 97931
P03 1801 73771
P03 1802 846420987
P03 1803 346987
P03 1804 744534
;
Run;

PROC SQL NOPRINT;
SELECT count(distinct EMPID)
INTO :N
FROM RawData;
QUIT;
%put &N ;/*3*/

PROC SQL NOPRINT;
SELECT count(distinct Ddate)
INTO :M
FROM RawData;
QUIT;
%put &M ;/*4*/

PROC SQL NOPRINT;
SELECT DISTINCT EMPID, Ddate
INTO :E1 - :&E&N., :D1 - :&D&M.
FROM RawData;
QUIT;
%put &E1 ;
%put &E2 ;
%put &E3 ;
%put &D1 ;
%put &D2 ;
%put &D3 ;
%put &D4 ;

Re: into statement in proc sql-get error

Kurt_Bremser — Wed, 06 Mar 2019 07:12:46 GMT

PLEASE use the "little running man" for posting code, the main posting window scrambled your code.

Why do you use a reference to macro variable E when you never defined it?

I guess you wanted

into :E1 - :E&N.,

Re: into statement in proc sql-get error

Astounding — Wed, 06 Mar 2019 07:19:09 GMT

If the first element in your list of macro variables is this:

E1

then the last element should be this:

&N

not this:

&E%N.

Also note, &N and &M contain leading blanks that you will need to remove. Here's the simple way to do that. Immediately after creating them (where you added %put statements) also add:

%let n = &n;

and

%let m = &m;

Re: into statement in proc sql-get error

Astounding — Wed, 06 Mar 2019 07:21:26 GMT

Sorry, should be this:

E&N

not this:

&E&N.

Re: into statement in proc sql-get error

Ronein — Wed, 06 Mar 2019 07:22:14 GMT

Great and thank you.

The solution is working great now after applying your tips

Data RawData;
Input  EMPID $3. Ddate $5. Y 9.;
cards;
P01 1801 258766    
P01 1802 92139678  
P01 1803 921396 
P01 1804 898755   
P02 1801 566511   
P02 1802 464467896 
P02 1803 87932
P02 1804 97931
P03 1801 73771  
P03 1802 846420987
P03 1803 346987 
P03 1804 744534   
;
Run;

PROC SQL NOPRINT;
SELECT count(distinct EMPID)
INTO :N    
FROM RawData;
QUIT; 
%put &N   ;/*3*/
%let N=&N.; /*To remove blanks*/

PROC SQL NOPRINT;
SELECT count(distinct Ddate)
INTO :M    
FROM RawData;
QUIT; 
%put &M   ;/*4*/
%let M=&M.; /*To remove blanks*/


 
PROC SQL NOPRINT;
SELECT DISTINCT EMPID, Ddate    
INTO :E1 - :E&N., :D1 - :D&M.    
FROM RawData;
QUIT; 
%put &E1   ; 
%put &E2   ;
%put &E3   ;
%put &D1   ;
%put &D2   ;
%put &D3   ;

Re: into statement in proc sql-get error

Kurt_Bremser — Wed, 06 Mar 2019 08:45:02 GMT

This will not give you your intended result:

PROC SQL NOPRINT;
SELECT DISTINCT EMPID, Ddate    
INTO :E1 - :E&N., :D1 - :D&M.    
FROM RawData;
QUIT; 
%put &E1   ; 
%put &E2   ;
%put &E3   ;
%put &D1   ;
%put &D2   ;
%put &D3   ;

See this log snippet:

68         %put &E1   ;
P01
69         %put &E2   ;
P01
70         %put &E3   ;
P01

Because the output of the select is this (if run without noprint):

EMPID  Ddate
------------
P01    1801 
P01    1802 
P01    1803 
P01    1804 
P02    1801 
P02    1802 
P02    1803 
P02    1804 
P03    1801 
P03    1802 
P03    1803 
P03    1804

The Ex macro variables catch the first three observations, the Dx the first four.

In order to get what you want (my guess), you need to run two separate select into's:

proc sql noprint;
select distinct empid
into :e1 - :e&n
from RawData;
select distinct ddate
into :d1 - :d&m
from RawData;
quit;

%put &e1;
%put &e2;
%put &e3;
%put &d1;
%put &d2;
%put &d3;
%put &d4;

Now the log reads:

36         %put &e1;
P01
37         %put &e2;
P02
38         %put &e3;
P03
39         %put &d1;
1801
40         %put &d2;
1802
41         %put &d3;
1803
42         %put &d4;
1804

Re: into statement in proc sql-get error

Kurt_Bremser — Wed, 06 Mar 2019 08:47:30 GMT

Note how this (once again) illustrates the non-usefulness of macro variable lists. Data belongs in datasets, and can very easily be transformed into usable dynamic entities (hash objects, formats, joins) WITHOUT the abuse of the macro preprocessor.

Re: into statement in proc sql-get error

RW9 — Wed, 06 Mar 2019 09:01:11 GMT

Pretty much all of it. All of which has been mentioned to you multiple times. Coding all in uppercase to start with, not using code window. Do not put data into macro variables, avoid macro loops as you already have the data available. End macro variables with a final dot.

Re: into statement in proc sql-get error

Kurt_Bremser — Wed, 06 Mar 2019 09:21:45 GMT

Just to add to what @RW9 said:

I am a data warehouse administrator / chief developer / maintainer for a quite large company for ~ 20 years now, and I did not even know about the "select - into" feature of SQL until some three years ago (I learned thanks to the communities) because I NEVER needed that. NEVER. And I still haven't used it anywhere in our production jobs. If I need to preserve a specific item in a macro variable, I use call symput, and I never use macro variable lists. Once again, NEVER.

Right now, I advise you to "forget" that the macro preprocessor even exists, and solve your issues with Base SAS. Your posts repeatedly show how abusing the macro preprocessor only serves to get in your way.