BookmarkSubscribeRSS Feed
harry_87
Calcite | Level 5

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

3 REPLIES 3
ballardw
Super User

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

harry_87
Calcite | Level 5

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

 

ballardw
Super User

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;

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

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
  • 3 replies
  • 683 views
  • 0 likes
  • 2 in conversation