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 ;
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.,
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 ;
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
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.
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.
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.
Available on demand!
Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.
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.