BookmarkSubscribeRSS Feed
jerry898969
Pyrite | Level 9
I have a table that looks like this
A
A
A
B
B

How can I create an id column that will look like this
1 A
1 A
1 A
2 B
2 B

Thank you for your help
6 REPLIES 6
RichardH_sas
SAS Employee
First./last. processing using DATA step code would be the easiest method. I can't think of a good way to do this via the query builder or a data task... but maybe someone else will come up with something there. 🙂

/* Create data as an example for this */
data temp;
input charcol : $1.;
datalines;
A
A
A
B
A
C
;
run;

/* Pre-sort the data by the variable in question, very important */
proc sort data=temp out=tempsort;
by charcol;
run;

/* Use a BY statement for the sorted variable */
data temp2;
set tempsort;
by charcol;
if first.charcol=1 then id+1;
run;
jerry898969
Pyrite | Level 9
Richard,
Thank you so much that was it.

I have to find a way to grasp the way sas handles data compared to other DBMS.

Thank you again
jerry898969
Pyrite | Level 9
Richard,
Sorry to bother but I have a follow up question based off of the first one.

Say now I have a second column that has data in it

A A
A A
A B
A C
B A
B C
C A
C A
C A
C B

I need the ids to do the same for the second column kind of like we did for the first.

A A 1 1
A A 1 1
A B 1 2
A C 1 3
B A 2 1
B C 2 2
C A 3 1
C A 3 1
C A 3 1
C B 3 2

Sorry if this is confusing. I have 4 columns that I have to id this way.

Thank You
jerry898969
Pyrite | Level 9
I have run into another issue with the way my data needs to be numbered and I can't seem to find a way to do it.

If I have
A A
A B
A
A C
A D

I need a way for my numbering to skip the blanks without adding 1 to the counter.

A A 1 1
A B 1 2
A 1
A C 1 3
A D 1 4

I need my counter to not increment on the 3rd row so the 4th row will continue from the id on the second row.

Any help would be greatly appreciated.

Thank You
jklein271
Calcite | Level 5
Something like this should work. May not be exactly what you are looking for, but let me know.

/* Create data as an example for this */
data temp;
infile datalines dsd delimiter=',';
attrib charcol1 length = $1
charcol2 length = $1;
input charcol1 $
charcol2 $;
datalines;
A,A
A,B
A,
A,C
A,D
;
run;

/* Pre-sort the data by the variable in question, very important */
proc sort data=temp out=tempsort;
by charcol1 charcol2;
run;

/* Use a BY statement for the sorted variable */
data temp2;
set tempsort;
retain charcol1 charcol2;
by charcol1 charcol2;
if first.charcol1 then id+1;
if first.charcol2 and charcol2 NE ' ' then id2+1;
if id2=0 then id2=.;
run;

A 1 .
A A 1 1
A B 1 2
A C 1 3
A D 1 4
jerry898969
Pyrite | Level 9
jklein,

Thank you so much for the help. Your code helped me get it working.

Thanks
Jerry

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!

SAS Enterprise Guide vs. SAS Studio

What’s the difference between SAS Enterprise Guide and SAS Studio? How are they similar? Just ask SAS’ Danny Modlin.

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
  • 6 replies
  • 934 views
  • 0 likes
  • 3 in conversation