Syntax error for the first.variables?

Accepted Solution Solved
Reply
Super Contributor
Posts: 274
Accepted Solution

Syntax error for the first.variables?

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;

 

 


Accepted Solutions
Solution
3 weeks ago
Respected Advisor
Posts: 4,978

Re: Syntax error for the first.variables?

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;

View solution in original post


All Replies
Esteemed Advisor
Esteemed Advisor
Posts: 7,222

Re: Syntax error for the first.variables?

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;
Super Contributor
Posts: 274

Re: Syntax error for the first.variables?

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.

 

Esteemed Advisor
Esteemed Advisor
Posts: 7,222

Re: Syntax error for the first.variables?

[ Edited ]

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).

Super Contributor
Posts: 274

Re: Syntax error for the first.variables?

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.

 

Esteemed Advisor
Posts: 6,669

Re: Syntax error for the first.variables?

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

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
Super Contributor
Posts: 274

Re: Syntax error for the first.variables?

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

 

Esteemed Advisor
Esteemed Advisor
Posts: 7,222

Re: Syntax error for the first.variables?

Where is the by line?

 

103 data translist1;

104 set translist1;

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

106 run;

Super Contributor
Posts: 274

Re: Syntax error for the first.variables?

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

 

Esteemed Advisor
Posts: 6,669

Re: Syntax error for the first.variables?

SORT, THEN USE "BY" !!!

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
Super Contributor
Posts: 274

Re: Syntax error for the first.variables?

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

Esteemed Advisor
Posts: 6,669

Re: Syntax error for the first.variables?

Just out of curiosity:

from where did you get the idea that

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

could work at all?

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
Solution
3 weeks ago
Respected Advisor
Posts: 4,978

Re: Syntax error for the first.variables?

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;

☑ This topic is SOLVED.

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

Discussion stats
  • 12 replies
  • 134 views
  • 0 likes
  • 4 in conversation