BookmarkSubscribeRSS Feed
user24
Obsidian | Level 7

How can i put '-' between these 2 variable for same id

 

id     number     code

3       25              a

3       12              a

3        10             a

4        15             a

 

new table i need

 

id    number   code

3      25-12-10      a

4         15        a

11 REPLIES 11
RW9
Diamond | Level 26 RW9
Diamond | Level 26

Please post test data in the form of a datastep, its very simple just follow this post.

https://communities.sas.com/t5/SAS-Communities-Library/How-to-create-a-data-step-version-of-your-dat...

I don't understand why you are so reluctant to do this?

 

As for your problem, I am not typing that in so this is general (and assumes number is character):

1) sort the data so first.id and last.id are the range

2) do something like:

data want;
  set have;
  length fst_val $20;
  retain fst_val;
  if first.id then fst_val=number;
  if last.id and not(first.id) then do;
    number=catx('-'.fst_val,number);
  end;
  else if first.id and last.id then output;
run;
PeterClemmensen
Tourmaline | Level 20

what if there were more than 2 instances of the same ID? 🙂

user24
Obsidian | Level 7

yes, thats'a good question. Thank you. I modified my question

Reeza
Super User

The amount of observations per group doesn't affect the solution. @RW9 solution will still work. 

 

Edit: Slight changes are required to condition. 

 

data want;
set have;
length fst_val $20;

 

retain fst_val;

 

if first.id then fst_val=number;
Else number=catx('-'.fst_val,number);

If last.id then output;
run;

user24
Obsidian | Level 7

data have;
input id $ number $;
datalines;
3 25
3 12
3 10
4 15
;quit;

data want;
set have;
length fst_val $20;

retain fst_val;

if first.id then fst_val=number;
Else number=catx('-',fst_val,number);

If last.id then output;
run;

Can you check the code, what is missing. Not working
RW9
Diamond | Level 26 RW9
Diamond | Level 26

Thank you for the test data, here is working code:

data have;
  input id $ number $;
datalines;
3 25
3 12
3 10
4 15
;
run;

proc sort data=have;
  by id number;
run;

data want;
  set have;
  by id;
  length fst_val $20;
  retain fst_val;
  if first.id then fst_val=number;
  if last.id and not(first.id) then do;
    number=catx('-',fst_val,number);
    output;
  end;
  else if first.id and last.id then output;
run;
user24
Obsidian | Level 7
it takes only 2 variable.
RW9
Diamond | Level 26 RW9
Diamond | Level 26

Sorry, are you asking to have all values within the list, as your original post seemed to indicate <min>-<max>.

If so then:

data have;
input id $ number $;
datalines;
3 25
3 12
3 10
4 15
;
quit;

proc sort data=have;
by id number;
run;

data want (drop=fst_val);
set have (rename=(number=fst_val));
by id;
length number $20;
retain number;
if first.id then number=fst_val;
else number=catx("-",number,fst_val);
if last.id then output;
run;

Do however note that the final string has to be long enough to store all the numbers in this instance.  For example your test data creates number as char(8), default when length not specified.  8 Characters is just about enough to hold 3 * 2 + 2 characters, but any more and you will have problems.  That is why in the program above I rename the incoming $8 variable and put the result in a new string which is $200 to hold a lot more possibilities.  

Astounding
PROC Star

I think a little more tweaking might be needed:

 

data want;
set have;
length fst_val $20;

retain fst_val;

 

if first.id then fst_val=number;
else fst_val=catx('-'.fst_val,number);

If last.id then output;

drop number;

rename fst_val=number;
run;

 

Peter_C
Rhodochrosite | Level 12

to a similar problem,I po0sted the brief data step which can be seen at https://communities.sas.com/t5/SAS-Procedures/Combine-multiple-observations-into-one-if-they-have-th...

Peter_C
Rhodochrosite | Level 12
might be better to just see the code adapted for this case
data out1 ( compress= yes keep= code collection ) ;
length collection $10000 ;
Do item=1 by 1 until( last.code ) ;
set your_data ;
by code ;
collection = catX( '-', collection, number ) ;
End ;
run ;

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
  • 11 replies
  • 1874 views
  • 1 like
  • 6 in conversation