topic Re: Put "-" between 2 variable in SAS Programming

Put "-" between 2 variable

user24 — Wed, 01 Feb 2017 13:38:23 GMT

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

Re: Put "-" between 2 variable

RW9 — Wed, 01 Feb 2017 13:18:29 GMT

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-data-AKA-generate/ta-p/258712

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;

Re: Put "-" between 2 variable

PeterClemmensen — Wed, 01 Feb 2017 13:18:39 GMT

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

Re: Put "-" between 2 variable

user24 — Wed, 01 Feb 2017 13:38:51 GMT

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

Re: Put "-" between 2 variable

Reeza — Wed, 01 Feb 2017 13:51:52 GMT

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;

Re: Put "-" between 2 variable

user24 — Wed, 01 Feb 2017 14:34:15 GMT

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

Re: Put "-" between 2 variable

Astounding — Wed, 01 Feb 2017 14:34:34 GMT

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;

Re: Put "-" between 2 variable

RW9 — Wed, 01 Feb 2017 14:42:41 GMT

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;

Re: Put "-" between 2 variable

user24 — Wed, 01 Feb 2017 14:45:51 GMT

it takes only 2 variable.

Re: Put "-" between 2 variable

RW9 — Wed, 01 Feb 2017 14:57:24 GMT

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.

Re: Put "-" between 2 variable

Peter_C — Wed, 01 Feb 2017 15:20:30 GMT

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-the-same-Id/m-p/329016#M62531

Re: Put "-" between 2 variable

Peter_C — Wed, 01 Feb 2017 16:05:36 GMT

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 ;