DATA Step, Macro, Functions and more

adding a new obs to existing dataset

Reply
Occasional Contributor
Posts: 16

adding a new obs to existing dataset

I need to add additional observations Balamt=0 where a the previous month (Monyear) value is null for a cateogy,

Would the best approach be to create  new table(see below) and then do a Union query?

data test;

   input monyear $ val $ Category $;

   datalines;

mar-15 0 1 mkt1

mar-15 0 2 mkt2

mar-15 0 1 mkt7

;

or could i create a if then data step to add the Balamt -0 if the previous months data is null?



Valued Guide
Posts: 858

Re: adding a new obs to existing dataset

Here  you go, hope this helps:

data have;

    infile datalines dsd;

    informat monyear2 date9.;

    format monyear2 date9.;

   input monyear $ monyear2 val $ Category $;

   datalines;

,31MAR2015,0 1,mkt1

mar-15,,0 2,mkt2

mar-15,,0 1,mkt7

;

run;

data want;

set have;

/*if using characters*/

if monyear = '' then balamt = '0';

/*if using numbers*/

if monyear2 = . then balamt2 = 1;

run;

Super User
Posts: 10,538

Re: adding a new obs to existing dataset

It will help if you can show some example data from the base data set and what the desired output for the example data would be.

Occasional Contributor
Posts: 16

Re: adding a new obs to existing dataset

below is an example of the current dataset and the desired data

current table

BALAMT MONYEAR Segment

100           Mar-15      agncy7

200           Mar-15      agncy2

300           Mar-15      agncy3

400           Mar-15      agncy1

100           Mar-15      sports4

needed table  

BALAMT MONYEAR Segment

0                Feb-14      agncy1

400           Mar-15         agncy1

0                Feb-14        agncy2

200           Mar-15           agncy2

0                Feb-14           agncy3

300           Mar-15           agncy3

0                Feb-14           agncy7

100           Mar-15           agncy7

0                Feb-14           sports4

100           Mar-15           sports4

Valued Guide
Posts: 858

Re: adding a new obs to existing dataset

I see what you meant about the union now, I misunderstood what you were asking for.  Yes I would build a data set with the date range first and then join.  I've used proc expand for this as well.

Super User
Posts: 5,092

Re: adding a new obs to existing dataset

If this is really an exact representation of the input and output, you could try:

data want;

   set have;

   output;

   balamt=0;

   monyear='Feb-14';

   output;

run;

Sorting might be necessary afterwards.  Good luck.

Ask a Question
Discussion stats
  • 5 replies
  • 276 views
  • 0 likes
  • 4 in conversation