topic Need to add column in dataset based on If condition in SAS Programming

Need to add column in dataset based on If condition

harry_87 — Mon, 21 May 2018 18:32:31 GMT

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

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

ballardw — Mon, 21 May 2018 19:14:25 GMT

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

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

harry_87 — Tue, 22 May 2018 05:10:19 GMT

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

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

ballardw — Tue, 22 May 2018 15:36:20 GMT

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;