DATA Step, Macro, Functions and more

How to insert a new observation in middle of the dataset using datastep

Accepted Solution Solved
Reply
Contributor
Posts: 37
Accepted Solution

How to insert a new observation in middle of the dataset using datastep

Hi,

I have trying to insert a new row after a specefic row, 

Like I have given below dataset and I want a row to be inserted after 3rd obeservation and 3 should be shifted to 4th.

 

I tried this-

 

Data have;

input name$;

if _N_ =3 then name="C1C1";

output;

cards;

AAA

BBB

CCC

DDD

;

run;

 

what output I have got is:

AAA

BBB

C1C1

DDD

 

What output I am looking for;

AAA

BBB

CCC

C1C1

DDD

 

Thanks!


Accepted Solutions
Solution
‎07-07-2016 01:08 PM
Super User
Posts: 11,343

Re: How to insert a new observation in middle of the dataset using datastep

Posted in reply to LittlesasMaster

Location is important for OUTPUT statements.

 


Data have;
input name$;
output;
if _N_ =3 then do;
   name="C1C1";
   output;
end;
cards;
AAA
BBB
CCC
DDD
;
run;

This reads, then outputs. If the line just output was 3 then output the extra line.

 

This may not work well if you intend to insert multiple values but may give you a start.

View solution in original post


All Replies
Solution
‎07-07-2016 01:08 PM
Super User
Posts: 11,343

Re: How to insert a new observation in middle of the dataset using datastep

Posted in reply to LittlesasMaster

Location is important for OUTPUT statements.

 


Data have;
input name$;
output;
if _N_ =3 then do;
   name="C1C1";
   output;
end;
cards;
AAA
BBB
CCC
DDD
;
run;

This reads, then outputs. If the line just output was 3 then output the extra line.

 

This may not work well if you intend to insert multiple values but may give you a start.

Contributor
Posts: 37

Re: How to insert a new observation in middle of the dataset using datastep

Thanks for your help.

any open suggestion for inserting multiple rows.

 

 

Super User
Posts: 11,343

Re: How to insert a new observation in middle of the dataset using datastep

[ Edited ]
Posted in reply to LittlesasMaster

You might need to describe in considerable detail what the insert rule(s) might be.

 

If you know the value that PRECEEDS the value you want to insert and the number isn't very large it may be possible to two temporary arrays to hold the key value and the following.

 


Data have;
   input name$;
   output;
   array key (3) $ 8 _temporary_ ('AAA','CCC','DDD');
   array add (3) $ 8 _temporary_ ('A2A2','C1C1','D3D');
   if whichc(name,of key(*))>0 then do;
      name= add[whichc(name,of key(*))];
      output;
   end;
cards;
AAA
BBB
CCC
DDD
;
run;

This works best if the number of elements in the two arrays match. If you insert the same value in multiple places it may be easier to duplicate them in the ADD array then to use complex logic, at least until you get the process working.

 

The length of the temporary values, the $ 8, in the arrays should be the same length as the declared variable name. You didn't assign one in this case so it was defaulting to 8. Best not to leave such things to chance. The order of the values in the arrays MUCH match for this to work.

Note that this approach will insert a row for each appearance of the Key value.

The values of any other variables on the record with Key value will be duplicated.

 

Another similar approach if you have very large number of inserts would be to have a separate dataset with the Key value as Name and the Add value as another variable. Use Merge (which should have the two sets sorted by Name) and the dataset option IN to test which sets contribute to the row values. If a row has both records contribute then output a second record.

Something like this.

data want;
   merge
      have (in=InHave)
      add  (in=InAdd)
   ;
   by name;
   output; 
   if InHave and InAdd then do;
      Name=InsertName;
      output;
   end;
run;

You might have to investigate MERGE for one-to-many and many-to-many matches to see if this would work.

 

 

If the inserts are to be in alphabetical order it may be easier to SET two datasets together and sort. Your example doesn't preclude that option.

 

   

☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 3 replies
  • 933 views
  • 1 like
  • 2 in conversation