DATA Step, Macro, Functions and more

Put "-" between 2 variable

Reply
Contributor
Posts: 33

Put "-" between 2 variable

[ Edited ]

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

Super User
Super User
Posts: 7,432

Re: Put "-" between 2 variable

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;
PROC Star
Posts: 555

Re: Put "-" between 2 variable

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

Contributor
Posts: 33

Re: Put "-" between 2 variable

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

Super User
Posts: 17,963

Re: Put "-" between 2 variable

[ Edited ]

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;

Contributor
Posts: 33

Re: Put "-" between 2 variable


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
Super User
Super User
Posts: 7,432

Re: Put "-" between 2 variable

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

Re: Put "-" between 2 variable

it takes only 2 variable.
Super User
Super User
Posts: 7,432

Re: Put "-" between 2 variable

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.  

Super User
Posts: 5,099

Re: Put "-" between 2 variable

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;

 

Valued Guide
Posts: 2,175

Re: Put "-" between 2 variable

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

Valued Guide
Posts: 2,175

Re: Put "-" between 2 variable

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 ;
Ask a Question
Discussion stats
  • 11 replies
  • 472 views
  • 1 like
  • 6 in conversation