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