topic Re: adding a new obs to existing dataset in SAS Programming

adding a new obs to existing dataset

thomask23 — Thu, 09 Apr 2015 17:10:16 GMT

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?

Re: adding a new obs to existing dataset

Steelers_In_DC — Thu, 09 Apr 2015 17:25:47 GMT

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;

Re: adding a new obs to existing dataset

ballardw — Thu, 09 Apr 2015 17:26:04 GMT

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.

Re: adding a new obs to existing dataset

thomask23 — Thu, 09 Apr 2015 18:36:52 GMT

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

Re: adding a new obs to existing dataset

Steelers_In_DC — Thu, 09 Apr 2015 19:30:27 GMT

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.

Re: adding a new obs to existing dataset

Astounding — Thu, 09 Apr 2015 19:48:03 GMT

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.