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

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;

 

 

1 ACCEPTED SOLUTION

Accepted Solutions
Astounding
PROC Star

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

12 REPLIES 12
RW9
Diamond | Level 26 RW9
Diamond | Level 26

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
Rhodochrosite | Level 12

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
Diamond | Level 26 RW9
Diamond | Level 26

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
Rhodochrosite | Level 12

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.

 

ybz12003
Rhodochrosite | Level 12

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
Diamond | Level 26 RW9
Diamond | Level 26

Where is the by line?

 

103 data translist1;

104 set translist1;

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

106 run;

ybz12003
Rhodochrosite | Level 12

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

 

ybz12003
Rhodochrosite | Level 12

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

Astounding
PROC Star

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;

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
  • 12 replies
  • 671 views
  • 0 likes
  • 4 in conversation