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;
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
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;
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;
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
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;
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???
Thus code may need an additional mechanism for extending the length of id.
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
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!
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.