DATA Step, Macro, Functions and more

Need to add column in dataset based on If condition

Reply
Occasional Contributor
Posts: 7

Need to add column in dataset based on If condition

I have one sas dataset so want to check of that value and column_position not exist then add that obs in dataset else if value of column is not exist and column_position is same then replace that col_position (for example at 15th row there is one observation so now I want to add new value to 15th row then original 15th row goes to 16th obs , 16th obs goes to 17th...etc (n+1 r lag function not sure) I am trying this, but not working properly, please help me

data test;
input value $ data_type $ length col_pos;
datalines;
harry varchar 100 1
henry number 100 2
homry varchar 100 3
;

%macro add_column(value,data_type,length,col_pos);

data _null_;
set test;
if value="&value" then call symput('col_name',1)
else call symput('col_name',2)
if col_pos="&col_pos" then call symput ('col_pos',1)
else call symput('col_pos,2)
run;

if &col_name=2 and &col_pos=2 then;
%do;
  data test;
set test;
value="&value";
data_type="&data_type";
length="&length";
col_pos="&col_pos";
output;
run;
%end;
%mend;
%add_column(alex,varchar,100,4);

Suppose if value changed and col_pos same For example, I need to replace 2nd col_pos and 2nd col_pos from test dataset above should go to 3rd %add_column(joe,varchar,100,2); if &col_name=2(as value is different) and &col_pos=1(col_pos is same i.e 2) then; (I am trying to write this but didn't worked) '2' col_pos is same

Please help me with this

Super User
Posts: 13,293

Re: Need to add column in dataset based on If condition

It may help to provide a small example of what you expect the output to look like.

Occasional Contributor
Posts: 7

Re: Need to add column in dataset based on If condition

Sorry, Actually I need to update dataset, I dont want to add new column (in short add rows/obs in a dataset)

If I execute below macro

%add_column(alex,varchar,100,4);

then output would be

Value Datatype length col_pos
harry varchar 100 1 henry number 100 2 homry varchar 100 3
alex varchar 100 4

  and If I execute below macro , 

%add_column(joe,varchar,100,2); 

then output would be

Value Datatype length col_pos
harry varchar 100 1
joe varchar 100 2 henry number 100 3 homry varchar 100 4

 

Super User
Posts: 13,293

Re: Need to add column in dataset based on If condition

Your data example implies the col_pos variable is an order in the data set variable and unique-one record only with a given value.. As such we could use that to UPDATE with BY col_pos as long as the data set is actually sorted by col_pos. The datastep update statement will add new records when the by variable value is not in the base data set.

Consider:

 

%macro add_column(value,data_type,length,col_pos);
/* get a single record to have the variable types/ format/lenghts*/
data work.temp;
   set test (obs=1);
run;
/* set the values from the parameters*/
data work.temp;
   set work.temp;
   value="&value.";
   data_type="&data_type.";
   length=&length.;
   col_pos=&col_pos.;
   output;
run;
/* update the data set*/
data test;
   update test
          work.temp;
   by col_pos;
run;

%mend;
Ask a Question
Discussion stats
  • 3 replies
  • 88 views
  • 0 likes
  • 2 in conversation