topic Re: Programming help required in SAS Programming

Programming help required

Sathya3 — Thu, 04 May 2023 18:04:16 GMT

I have two columns id,type
Each Id can have more than one type. Whenever Id has more than one type, type has to be concatenated with ID. When ID has only one type ,then type should not be concatenated with ID
Sample input data
ID Type
1 A
1 B
2 A
3 D
Output expected :
ID Type
1A A
1B B
2 A
3 D

Re: Programming help required

PaigeMiller — Thu, 04 May 2023 18:16:38 GMT

Seems like a homework assignment. Show us what you have tried.

Re: Programming help required

Sathya3 — Thu, 04 May 2023 18:33:26 GMT

It is part of a huge requirement.I have simplified it for easier understanding.If you can provide the code for that ,it would be great.

Re: Programming help required

PaigeMiller — Thu, 04 May 2023 18:38:41 GMT

Assumes the data is sorted by ID (which you didn't say, so is that a valid assumption?)

data want;
    length id $ 8;
    set have;
    by id;
    if not (first.id and last.id) then id=cats(id,type);
run;

Re: Programming help required

Reeza — Thu, 04 May 2023 18:39:29 GMT

You can use this method and then merge the results back with the original data for the duplicates.

The other option is a DoW loop but it will still do two passes of the data.

https://gist.github.com/statgeek/d583cfa992bf56da51d435165b07e96a

Re: Programming help required

Quentin — Thu, 04 May 2023 18:40:00 GMT

It's hard to help you, without knowing what you're learning. For example, have you learned about the BY statement?

Seeing how you approach the problem will help people help you. Are you thinking to do this with a DATA step? Or use PROC SQL? Or PROC FREQ? Even if your code is not working, sharing it will help.

Also you should post working SAS code to create the example input data. Typically that is done with a DATA step with a CARDS statement. That saves people time in having to write that code themselves.

Re: Programming help required

Kurt_Bremser — Thu, 04 May 2023 19:29:48 GMT

Is ID numeric or character? If character, what is its defined length?

Re: Programming help required

Sathya3 — Fri, 05 May 2023 04:49:23 GMT

Hi, I have tried this..But it is not giving me desired output.Might be some complex programming is required for my requirement to be fulfilled.

Requirement is "If I have multiple Types for an ID ,then Type must be concatenated to ID field.

But, if we have only one type for an ID , then type need not be concatenated with ID field.Here,Jan has multiple types (Class1 and class2) .Hence, type must be concatenated with ID like Janclass1 ,Janclass2. But for feb and mar , concatenation need not happen since they have only one value for type ( class3 type for feb and class4 type for mar).

The expected output should have following ID values : janclass1 , janclass1,janclass2,feb,feb,mar

Please do not use Origin field in the logic to acheive this.

data one;
input ID$ Type$ origin$;
datalines;
jan class1 UK
jan class1 UAE
jan class2 US
feb class3 INDIA
feb class3 MIDEAST
mar class4 NORWAY
;
run;

Proc sql;
create table temp as
Select ID,Type,count(*) as N from one
group by ID,Type;
quit;

data two;
set temp;
if N=1 then ID=cats(ID,Type);
run;

Re: Programming help required

Sathya3 — Fri, 05 May 2023 04:50:48 GMT

Hi, I have tried this..But it is not giving me desired output.Might be some complex programming is required for my requirement to be fulfilled.

Requirement is "If I have multiple Types for an ID ,then Type must be concatenated to ID field.

The expected output should have following ID values : janclass1 , janclass1,janclass2,feb,feb,mar

Please do not use Origin field in the logic to acheive this.

data one;
input ID$ Type$ origin$;
datalines;
jan class1 UK
jan class1 UAE
jan class2 US
feb class3 INDIA
feb class3 MIDEAST
mar class4 NORWAY
;
run;

Proc sql;
create table temp as
Select ID,Type,count(*) as N from one
group by ID,Type;
quit;

data two;
set temp;
if N=1 then ID=cats(ID,Type);
run;

How to solve this/

Sathya3 — Fri, 05 May 2023 06:00:46 GMT

Hi, I have tried this. But it is not giving me desired output. Might be some complex programming is required for my requirement to be fulfilled. Requirement is "If I have multiple Types for an ID ,then Type must be concatenated to ID field. But, if we have only one type for an ID , then type need not be concatenated with ID field.Here,Jan has multiple types (Class1 and class2) .Hence, type must be concatenated with ID like Janclass1 ,Janclass2. But for feb and mar , concatenation need not happen since they have only one value for type ( class3 type for feb and class4 type for mar). The expected output should have following ID values : janclass1 , janclass1,janclass2,feb,feb,mar Please do not use Origin field in the logic to achieve this.

data one;
input ID$ Type$ origin$;
datalines;
jan class1 UK
jan class1 UAE
jan class2 US
feb class3 INDIA
feb class3 MIDEAST
mar class4 NORWAY
;
run;

Proc sql;
create table temp as
Select ID,Type,count(*) as N from one 
group by ID,Type;
quit;

data two;
set temp;
if N=1 then ID=cats(ID,Type);
run;

Re: How to solve this/

ballardw — Fri, 05 May 2023 06:47:37 GMT

Please do not post identical questions.

And how is this different than your question in https://communities.sas.com/t5/SAS-Programming/Programming-help-required/m-p/873986#M345268

Re: How to solve this/

yabwon — Fri, 05 May 2023 07:54:27 GMT

Search Google for "double DoW-loop".

data one;
input ID$ Type$ origin$;
datalines;
jan class1 UK
jan class1 UAE
jan class2 US
feb class3 INDIA
feb class3 MIDEAST
mar class4 NORWAY
;
run;

data want;
  length newID $ 16;
  do _N_ = 1 by 1 until(last.id);
    set one;
    by id notsorted;

    if first.id then n_type=0;
    if Type NE lag(Type) then n_type+1;
  end;

  do _N_ = 1 to _N_;
    set one;
    if n_type > 1 then newID = cats(ID,Type);
                  else newID = ID;
    output;
  end;

  drop ID n_type;
  rename newID = ID;
run;
proc print;
run;

Bart

Re: Programming help required

yabwon — Fri, 05 May 2023 07:55:14 GMT

Look here:

https://communities.sas.com/t5/SAS-Enterprise-Guide/How-to-solve-this/m-p/874066/highlight/false#M42860

Bart

Re: How to solve this/

PaigeMiller — Fri, 05 May 2023 09:51:13 GMT

Hello @Sathya3

I'd like to request you provide meaningful (but brief) descriptions of the actual problem you are trying to solve as the title/subject of your post. Subject lines like "how to solve this" and "programming help required" are not helpful and don't describe the actual problem and could apply to virtually every post here in the SAS forums. We're trying to help you, please go ahead from now on and help us by creating meaningful description of the problem in the subject line

Re: Programming help required

Kurt_Bremser — Fri, 05 May 2023 11:05:00 GMT

select id, count(distinct type) as n
from have
group by id
having n > 1;

will give you a dataset with the id values for which you want concatenating. In the follow-up step, you need to create a new id variable with sufficient length to accommodate the concatenated strings.

Re: How to solve this/

Kurt_Bremser — Fri, 05 May 2023 11:36:40 GMT

I combined the posts.

Re: How to solve this/

Quentin — Fri, 05 May 2023 13:21:11 GMT

Thanks for sharing your code, and sample data.

You code is very close to working. I only see two problems.

In the SQL step, you are using count(*) grouped by ID and Type. This will tell you that there are two records for jan class1 and one record for jan class2. But it doesn't tell you that there are two different values of TYPE for jan. For that, you can use count(distinct(type)) grouped by ID.

In the data step, you need to increase the length of ID, or the value will be truncated.

I think this code will give what you want:

data one;
input ID$ Type$ origin$;
datalines;
jan class1 UK
jan class1 UAE
jan class2 US
feb class3 INDIA
feb class3 MIDEAST
mar class4 NORWAY
;
run;

Proc sql;
  create table temp as
  Select *,count (distinct(Type)) as N from one 
  group by ID;
quit;

data two;
  length ID $12 ;
  set temp;
  if N>1 then ID=cats(ID,Type);
run;

proc print data=two ;
run ;