topic Re: Macro Errors - PROC SQL and INTO in SAS Programming

Macro Errors - PROC SQL and INTO

LL5 — Tue, 09 May 2017 22:08:10 GMT

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;

41 %PUT &UNIT_TEST;

UNIT1 UNIT1 UNIT3 UNIT4 UNIT5

43 /* THIS PART DIDN'T WORK */

44 PROC SQL;

45 SELECT

46 SUBGROUP,

47 &UNIT_TEST

48 FROM GROUP

____

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

_____

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";

Re: Proc sql select into

Reeza — Tue, 09 May 2017 22:04:19 GMT

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;

Re: Macro Errors - PROC SQL and INTO

ballardw — Tue, 09 May 2017 22:21:56 GMT

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 ' '

Re: Macro Errors - PROC SQL and INTO

Astounding — Tue, 09 May 2017 23:29:20 GMT

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.

Re: Macro Errors - PROC SQL and INTO

RW9 — Wed, 10 May 2017 09:13:59 GMT

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.