topic How to solve this? in SAS Programming

How to solve this?

Sathya3 — Fri, 05 May 2023 05:00:02 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;

How to solve this?

Sathya3 — Fri, 05 May 2023 05:13: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.

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: How to solve this?

whymath — Fri, 05 May 2023 06:27:16 GMT

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;

Re: How to solve this?

Sathya3 — Fri, 05 May 2023 06:40:04 GMT

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

Re: How to solve this?

A_Kh — Fri, 05 May 2023 14:47:56 GMT

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;

Re: How to solve this?

ballardw — Fri, 05 May 2023 16:26:18 GMT

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???

Re: How to solve this?

Kurt_Bremser — Fri, 05 May 2023 16:30:50 GMT

Thus code may need an additional mechanism for extending the length of id.

Re: How to solve this?

whymath — Sat, 06 May 2023 01:40:10 GMT

Absolutely right!

Re: How to solve this?

whymath — Sat, 06 May 2023 01:43:03 GMT

I also update your sql code, please check it. And as @Kurt_Bremser reminded, you need to expand the length of ID.

Re: How to solve this?

Tom — Sat, 06 May 2023 03:47:53 GMT

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