Syntax error for the first.variables?

[ybz12003]

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;

[RW9]

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;

[ybz12003]

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.

[RW9]

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

[ybz12003]

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.

[Kurt_Bremser]

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

[ybz12003]

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

[RW9]

Where is the by line?

103 data translist1;

104 set translist1;

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

106 run;

[ybz12003]

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

[Kurt_Bremser]

SORT, THEN USE "BY" !!!

[ybz12003]

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

[Kurt_Bremser]

Just out of curiosity:

from where did you get the idea that

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

could work at all?

[Astounding · Accepted solution]

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;