topic Re: Syntax error for the first.variables? in SAS Programming

Syntax error for the first.variables?

ybz12003 — Wed, 05 Jul 2017 12:15:44 GMT

Hello:

I would like to create a varlist base on the first observation of name3. I got syntax error. Please let me know where I have did wrong, thanks.

data test1;

input Names:$100.;

datalines;

dx_1

dx_10

dx_80

state_id_5

state_id_9

if_mg_hk_4

if_mg_hk_12

if_mg_hk_30

;

run;

data translist1;

set test1;

num=compress(names,'_','A');

name3=cats(prxchange('s/\_\d+//',-1,compress(names)));

run;

proc SQL;

Select where first.name3 into tranlist3 FROM translist1;

----- ---------

22 79

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

<>, =, >, >=, ?, AND, BETWEEN, CONTAINS, EQ, EQT, FROM, GE, GET, GT, GTT, LE, LET,

LIKE, LT, LTT, NE, NET, OR, ^=, |, ||, ~=.

ERROR 79-322: Expecting a :.

quit;

Re: Syntax error for the first.variables?

RW9 — Wed, 05 Jul 2017 12:24:11 GMT

First.variable and Last.variable are datastep constructs not SQL. SQL has no concept of first or last, or by group processing. Do your processing in a datastep:

data _null_;
  set translist1;
  by name3;
  if first.name3 then call symput('tranlist3',name3);
run;

Re: Syntax error for the first.variables?

Kurt_Bremser — Wed, 05 Jul 2017 12:32:39 GMT

Just out of curiosity:

from where did you get the idea that

proc SQL;
Select where first.name3 ......

could work at all?

Re: Syntax error for the first.variables?

Astounding — Wed, 05 Jul 2017 12:33:58 GMT

I could be wrong about this, but I'm interpreting your question slightly differently. If you are looking to get a subset of all the possible NAME3 values (excluding duplicates), it could be done this way:

proc sql;

create table translist3 as

select distinct name3 from translist1;

quit;

That creates a data set. If you are looking to get a macro variable instead, it would look a bit different:

proc sql;

select distinct name3 into : translist3 separated by ' ' from translist1;

quit;

Re: Syntax error for the first.variables?

ybz12003 — Wed, 05 Jul 2017 12:38:18 GMT

Hi, RW9:

I use call symput based on your suggestion. However I still got an error message.

data translist1;

set test1;

num=cats(compress(names,'_','A'));

name3=cats(prxchange('s/\_\d+//',-1,compress(names)));

if first.name3 then call symput('tranlist3',name3);

run;

NOTE: Variable first.name3 is uninitialized.

Re: Syntax error for the first.variables?

RW9 — Wed, 05 Jul 2017 12:51:25 GMT

Actually, looking further you can't do that as your creating names3. What exactly is it your trying to achieve, post test data and required output. Would be simpler than trying to code round each connotation you haven't presented.

Missing the

  by name3;

Line. First and last is a flag created in the data based on the by group(s).

Re: Syntax error for the first.variables?

ybz12003 — Wed, 05 Jul 2017 13:12:13 GMT

Still don't work.

ERROR: BY variables are not properly sorted on data set WORK.TRANSLIST1.

Names=state_id_9 num=9 name3=state_id first.name3=1 LAST.name3=0 _ERROR_=1 _N_=4

NOTE: The SAS System stopped processing this step because of errors.

NOTE: There were 5 observations read from the data set WORK.TRANSLIST1.

WARNING: The data set WORK.TRANSLIST1 may be incomplete. When this step was stopped there were 3

observations and 3 variables.

WARNING: Data set WORK.TRANSLIST1 was not replaced because this step was stopped.

Re: Syntax error for the first.variables?

Kurt_Bremser — Wed, 05 Jul 2017 13:15:24 GMT

Using by group processing requires that the dataset(s) are sorted by the variables named in the by statement. That's it.

Re: Syntax error for the first.variables?

ybz12003 — Wed, 05 Jul 2017 13:18:26 GMT

102 proc sort data=translist1; by name3; run;

NOTE: There were 5 observations read from the data set WORK.TRANSLIST1.

NOTE: The data set WORK.TRANSLIST1 has 5 observations and 3 variables.

NOTE: PROCEDURE SORT used (Total process time):

real time 0.01 seconds

cpu time 0.00 seconds

103 data translist1;

104 set translist1;

105 if first.name3 then call symput('tranlist3',name3);

106 run;

NOTE: Variable first.name3 is uninitialized.

NOTE: There were 5 observations read from the data set WORK.TRANSLIST1.

NOTE: The data set WORK.TRANSLIST1 has 5 observations and 3 variables.

NOTE: DATA statement used (Total process time):

real time 0.01 seconds

cpu time 0.00 seconds

Re: Syntax error for the first.variables?

RW9 — Wed, 05 Jul 2017 13:22:37 GMT

Where is the by line?

103 data translist1;

104 set translist1;

105 if first.name3 then call symput('tranlist3',name3);

106 run;

Re: Syntax error for the first.variables?

ybz12003 — Wed, 05 Jul 2017 13:28:47 GMT

129 data translist1;

130 set translist1;

131 if first.name3 then call symput('tranlist3',name3); by name3;

132 run;

ERROR: BY variables are not properly sorted on data set WORK.TRANSLIST1.

Names=state_id_9 num=9 name3=state_id first.name3=1 LAST.name3=0 _ERROR_=1 _N_=4

NOTE: The SAS System stopped processing this step because of errors.

NOTE: There were 5 observations read from the data set WORK.TRANSLIST1.

WARNING: The data set WORK.TRANSLIST1 may be incomplete. When this step was stopped there were 3

observations and 3 variables.

WARNING: Data set WORK.TRANSLIST1 was not replaced because this step was stopped.

NOTE: DATA statement used (Total process time):

real time 0.01 seconds

cpu time 0.01 seconds

Re: Syntax error for the first.variables?

Kurt_Bremser — Wed, 05 Jul 2017 13:31:31 GMT

SORT, THEN USE "BY" !!!

Re: Syntax error for the first.variables?

ybz12003 — Wed, 05 Jul 2017 13:31:58 GMT

I got it, I need to proc sort name3 first. Then use by name3 on the data step. Thanks.