Programming help required

[Sathya3]

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

[PaigeMiller]

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

[Sathya3]

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.

[Reeza]

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

[Quentin]

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.

[Sathya3]

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;

[Kurt_Bremser]

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.

[PaigeMiller]

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;

[Sathya3]

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;

[Kurt_Bremser]

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

[Sathya3]

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;

[ballardw]

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

[Kurt_Bremser]

I combined the posts.

[yabwon]

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

[PaigeMiller]

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