BookmarkSubscribeRSS Feed
☑ This topic is solved. Need further help from the community? Please sign in and ask a new question.
Sathya3
Obsidian | Level 7

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;

 

1 ACCEPTED SOLUTION

Accepted Solutions
Tom
Super User Tom
Super User

Seems like a silly thing to do.  Just use ID and TYPE as the key variables.  No need to mess either one of them up by adding other things to them.

 

Here is simple SQL query that will do what you asked for.

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
;

proc sql;
create table want as
select case when count(distinct type)=1 then id
            else cats(id,type)
       end as ID
     , *
from one
group by ID
;
quit;

Result

Obs    ID            Type     origin

 1     feb          class3    MIDEAST
 2     feb          class3    INDIA
 3     janclass2    class2    US
 4     janclass1    class1    UK
 5     janclass1    class1    UAE
 6     mar          class4    NORWAY

View solution in original post

9 REPLIES 9
Sathya3
Obsidian | Level 7

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;
whymath
Lapis Lazuli | Level 10

A little modification to your code:

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

data two;
set temp;
if N^=1 then ID=cats(ID,Type);
run;
Sathya3
Obsidian | Level 7

if I type N^=1 , jan is not appended with class2 and feb is appended with class3.This is not desired ouput.

 

Below is the desired output of ID column

 

janclass1
janclass1
janclass2
feb 
feb 
mar 

 

 

A_Kh
Lapis Lazuli | Level 10
This code works only for this given dataset, based on your real data and expectations it could be developed further, like creating macrovar containing multiple values and using it in data step with IN operator etc.. 
proc sort data=one;
by id type;
run; 

data _null_;
	set one;
	by id type;
	if not last.id and last.type then call symputx('value', strip(id));
run;
%put &=value;

data want;
	length id $15;
	set one(rename=(id=_id));
	if strip(_id) eq "&value" then id= cats(_id, type);
	else id = _id;
	drop _id;
proc print;run; 
ballardw
Super User

Please show the complete code of a data step or procedure call. Better is to capture the result from the LOG and share the log. Copy the text from the log of the code an all related messages. Then on the forum open a text box and paste the text. The text box preserves formatting of text and will help with the diagnostic characters SAS sometimes displays with problems.

 


@Sathya3 wrote:

if I type N^=1 , jan is not appended with class2 and feb is appended with class3.This is not desired ouput.

Why should typing N^=1 do anything? You show an incomplete statement, no operations done, you don't even show an IF . Your example data does not include any variable named N. So the value of the just created variable N will be missing and likely shows a message in the log:

NOTE: Variable N is uninitialized.

which tells you there is no value assigned to N.

 

Did you mean the SAS automatic variable _N_ that counts iterations of the data step and in simple code indicates the row number of an observation in a data set???

whymath
Lapis Lazuli | Level 10
I also update your sql code, please check it. And as @Kurt_Bremser reminded, you need to expand the length of ID.
whymath
Lapis Lazuli | Level 10
Absolutely right!
Tom
Super User Tom
Super User

Seems like a silly thing to do.  Just use ID and TYPE as the key variables.  No need to mess either one of them up by adding other things to them.

 

Here is simple SQL query that will do what you asked for.

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
;

proc sql;
create table want as
select case when count(distinct type)=1 then id
            else cats(id,type)
       end as ID
     , *
from one
group by ID
;
quit;

Result

Obs    ID            Type     origin

 1     feb          class3    MIDEAST
 2     feb          class3    INDIA
 3     janclass2    class2    US
 4     janclass1    class1    UK
 5     janclass1    class1    UAE
 6     mar          class4    NORWAY

SAS Innovate 2025: Call for Content

Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!

Submit your idea!

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