BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
LL5
Pyrite | Level 9 LL5
Pyrite | Level 9

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

 

1 ACCEPTED SOLUTION

Accepted Solutions
Astounding
PROC Star

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

4 REPLIES 4
Reeza
Super User

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;

ballardw
Super User
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 ' '

 

Astounding
PROC Star

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.

RW9
Diamond | Level 26 RW9
Diamond | Level 26

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.  

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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