DATA Step, Macro, Functions and more

Macro Errors - PROC SQL and INTO

Accepted Solution Solved
Reply
Contributor LL5
Contributor
Posts: 44
Accepted Solution

Macro Errors - PROC SQL and INTO

[ Edited ]

Hi, I tried the below codes but the last part didn't work, syntax errors showing as below.

Can anyone advise on it?

Thanks

 

 

DATA GROUP;
	INPUT GROUP$ SUBGROUP$ UNIT$;
	CARDS;
GROUP1 SUBGROUP1 UNIT1
GROUP1 SUBGROUP2 UNIT1
GROUP1 SUBGROUP3 UNIT3
GROUP4 SUBGROUP4 UNIT4
GROUP5 SUBGROUP5 UNIT5
;
RUN;

PROC SQL;
	SELECT UNIT INTO: UNIT_TEST
		SEPARATED BY ' '
	FROM GROUP;
QUIT;

%PUT &UNIT_TEST;

/* THIS PART DIDN'T WORK */
PROC SQL;
	SELECT
		SUBGROUP,
		&UNIT_TEST
	FROM GROUP
		WHERE GROUP = "GROUP1";
QUIT;

 

40        

41         %PUT &UNIT_TEST;

UNIT1 UNIT1 UNIT3 UNIT4 UNIT5

42        

43         /* THIS PART DIDN'T WORK */

44         PROC SQL;

45         SELECT

46         SUBGROUP,

47         &UNIT_TEST

48         FROM GROUP

           ____

           22

           201

ERROR 22-322: Syntax error, expecting one of the following: a name, ;, (, ',', '.', ANSIMISS, AS, CROSS, EXCEPT, FULL, GROUP,

              HAVING, INNER, INTERSECT, JOIN, LEFT, NATURAL, NOMISS, ORDER, OUTER, RIGHT, UNION, WHERE. 

 

ERROR 201-322: The option is not recognized and will be ignored.

 

NOTE: Line generated by the macro variable "UNIT_TEST".

48          UNIT1 UNIT1 UNIT3 UNIT4 UNIT5

                  _____

                  22

                  202

ERROR 22-322: Syntax error, expecting one of the following: a quoted string, !, !!, &, (, *, **, +, ',', -, '.', /, <, <=, <>, =,

              >, >=, ?, AND, AS, BETWEEN, CONTAINS, EQ, EQT, FORMAT, FROM, GE, GET, GT, GTT, IN, INFORMAT, INTO, IS, LABEL, LE,

              LEN, LENGTH, LET, LIKE, LT, LTT, NE, NET, NOT, NOTIN, OR, TRANSCODE, ^, ^=, |, ||, ~, ~=. 

 

ERROR 202-322: The option or parameter is not recognized and will be ignored.

 

49         WHERE GROUP = "GROUP1";

 


Accepted Solutions
Solution
‎05-10-2017 07:37 AM
Super User
Posts: 5,516

Re: Macro Errors - PROC SQL and INTO

It's much more than a matter of adding commas.  Assuming you did add commas in the right places, you would still have PROC SQL code that looks like this:

 

proc sql;

select subgroup, UNIT1, UNIT1, UNIT3, UNIT4, UNIT5

from group

where group='GROUP1';

quit;

 

Clearly, this is not a valid program.  Your incoming data set does not contain variables named UNIT1, UNIT3, UNIT4, or UNIT5.  Your proper steps at this point begin with designing a working SAS program.  With that as the objective, only then might macro language be able to help.

View solution in original post


All Replies
Super User
Posts: 19,855

Re: Proc sql select into

Macro syntax must be valid. Use FIND/REPLACE to see your code generated or read the log for the error. Your 'units' need to have a comma inbetween them, so in the SEPARATED BY change that to include a comma between the fields.

 

Original:

PROC SQL;

SELECT

SUBGROUP,

&UNIT_TEST

FROM GROUP

WHERE GROUP = "GROUP1";

QUIT;

 

Resolved:

 

PROC SQL;

SELECT

SUBGROUP,

UNIT1 UNIT1 UNIT3 UNIT4 UNIT5 <- Fix this to have comma's in between

FROM GROUP

WHERE GROUP = "GROUP1";

QUIT;

Super User
Posts: 11,343

Re: Macro Errors - PROC SQL and INTO

PROC SQL noprint;
	SELECT UNIT INTO: UNIT_TEST
		SEPARATED BY ', '
	FROM GROUP;
QUIT;

If you need to separate lists with the same content but one with commas and one without then make two separate macro variables with different delimiter ',' or ' '

 

Solution
‎05-10-2017 07:37 AM
Super User
Posts: 5,516

Re: Macro Errors - PROC SQL and INTO

It's much more than a matter of adding commas.  Assuming you did add commas in the right places, you would still have PROC SQL code that looks like this:

 

proc sql;

select subgroup, UNIT1, UNIT1, UNIT3, UNIT4, UNIT5

from group

where group='GROUP1';

quit;

 

Clearly, this is not a valid program.  Your incoming data set does not contain variables named UNIT1, UNIT3, UNIT4, or UNIT5.  Your proper steps at this point begin with designing a working SAS program.  With that as the objective, only then might macro language be able to help.

Super User
Super User
Posts: 7,980

Re: Macro Errors - PROC SQL and INTO

Please follow the guidance you find below the post button when you write a new question.  Post example test data in the form of a datastep and what you want the output to look like.  Your code as you post it makes no sense, UNITx are noot columns, therefore you cannot "select" them.  If you show what you are trying to do, its likely there is a far simpler method, for instance, it appears your trying to transpose them, so:

data group;
  length group subgroup unit $50;
	input group $ subgroup $ unit $;
cards;
GROUP1 SUBGROUP1 UNIT1
GROUP1 SUBGROUP2 UNIT2
GROUP1 SUBGROUP3 UNIT3
GROUP4 SUBGROUP4 UNIT4
GROUP5 SUBGROUP5 UNIT5
;
run;
proc transpose data=group out=want;
  by group;
  var subgroup;
  id unit;
  idlabel unit;
run;

Note I have had to correct your test data as you had two UNIT1's in the group, and your test data did not read in correctly.

Also, please dont' code in capitals, it makes it difficult to read.  

☑ This topic is solved.

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

Discussion stats
  • 4 replies
  • 137 views
  • 3 likes
  • 5 in conversation