topic Re: data generation in SAS Data Management

data generation

Kyojik — Mon, 10 Apr 2017 15:32:19 GMT

Hello Friends. I have follwing problem. I hope there will be some easy way to do this in sas.

thanks in advance

I want to create a variable on monthly basis but my data have inconsistent intervalas. Like if I have a value in decemebr 2015 as B and next value is on June 2015 as C and then January, 2016 as A. what I want to do is create a file in which I will have all the missing year and month columns with previous available value. Like

Data Type Have:

CompanyName Year month TYPE

Abc 2016 1 A

ABC 2016 10 C

Abc 2015 2 B

WANT TYPE :

abc	2016	1	A
abc	2016	2	A
abc	2016	3	A
abc	2016	4	A
abc	2016	5	A
abc	2016	6	A
abc	2016	7	A
abc	2016	8	A
abc	2016	9	A
abc	2016	10	C
abc	2016	11	C
abc	2016	12	C

abc	2015	2	B
abc	2015	3	B
abc	2015	4	B
abc	2015	5	B
abc	2015	6	B
abc	2015	7	B
abc	2015	8	B
ABC	2015	9	B
Abc	2015	10	B
ABC	2015	11	B
abc	2015	12	B

Re: data generation

RW9 — Mon, 10 Apr 2017 15:46:21 GMT

Post test data in the form of a datastep.

As such, this is just an example:

proc sort data=have out=years nodupkey;
  by year;
run;

data years;
  set years;
  do month=1 to 12;
    output;
  end;
run;

proc sql;
  create table WANT as
  select A.NAME,
            A.YEAR,
            B.MONTH,
            B.TYPE
  from   HAVE A
  full join YEARS B
  on      A.YEAR=B.YEAR 
  and    A.MONTH >= B.MONTH;
quit;

Re: data generation

TomKari — Mon, 10 Apr 2017 15:51:43 GMT

There are many ways to do this.

Here's one that's a little long, but the logic is clear.

Tom

/* Set up a reference date of first of the month */

data Have;

set Have;

TestDate = mdy(Month, 1, Year);

drop Year Month;

run;

/* Generate every first of the month from Jan 1 2014 to Dec 31 2017 */

data AllMonths;

do Year = 2014 to 2017;

do Month = 1 to 12;

DateToCheck = mdy(Month, 1, Year);

format DateToCheck date.;

keep DateToCheck;

output;

end;

/* Join the two, to get a full list of months with Type */

proc sql noprint;

create table Want as

select * from Have h right join AllMonths a on(h.TestDate = a.DateToCheck)

order by a.DateToCheck;

quit;

/* Ripple the values of CompanyName and Type */

data Want;

set Want;

length OldCompanyName $8 OldType $1;

retain OldCompanyName OldType;

if ^missing(Type) then

OldType = Type;

else Type = OldType;

if ^missing(CompanyName) then

OldCompanyName = CompanyName;

else CompanyName = OldCompanyName;

drop OldCompanyName OldType TestDate;

run;

Re: data generation

Kyojik — Mon, 10 Apr 2017 16:08:41 GMT

Thanks a lot you ace it.

Re: data generation

Kyojik — Mon, 10 Apr 2017 16:09:31 GMT

Thanks for your help unfortunately it was not what I was looking for. someone else solved this. Thanks a lot

Re: data generation

novinosrin — Mon, 10 Apr 2017 18:43:58 GMT

hi.

data have;

input CompanyName $ Year month TYPE $;

datalines;

Abc 2016 1 A

Abc 2016 10 C

Abc 2015 2 B

;

proc sort data=have;

by companyname year month;

run;

data want(rename=(_month=month));

length _type _type1 $1;

do _n_=1 by 1 until(last.year);

set have;

by year ;

if first.year and last.year then

do;

do _month=month to 12;

output;

end;

if _n_=1 and not last.year then

do;

call missing(_month,_type,_month1,_type1);

_month=month;

_type=type;

end;

if _n_>1 then

do;

_type1=type ;

do _month=_month to month-1;

_month1=month;

type=_type;

output;

end;

type=_type1;

output;

end;

if (_n_>1 and last.year and month<=12) or (first.year and last.year) then

do;

do _month=_month+1 to 12;

output;

end;

drop month _type _type1 _month1;

run;

Regards,

Naveen Srinivasan

Re: data generation

Kyojik — Thu, 20 Apr 2017 13:48:47 GMT

Thank you for your help. This works perfect when there is one company. how can I do this with multiple companies ?
I tried to add company name in by statement but it's not working in this way.
thank you very much again.

Re: data generation

TomKari — Thu, 20 Apr 2017 14:43:20 GMT

It's a little trickier than that...

Instead of just having one list of "first of the month" dates, we need to have a list of these dates for each company name. Then, after the merge, we have to modify the ripple to reset for each new company.

I think this is pretty close: (not fully tested)

Tom

/* Set up a reference date of first of the month */

data Have;

set Have;

TestDate = mdy(Month, 1, Year);

format TestDate date9.;

drop Year Month;

run;

/* Get a list of unique company names */

proc sql noprint;

create table Names as

select distinct CompanyName from Have h;

quit;

/* Generate every first of the month from Jan 1 2014 to Dec 31 2017 */

data AllMonths;

do Year = 2014 to 2017;

do Month = 1 to 12;

DateToCheck = mdy(Month, 1, Year);

format DateToCheck date9.;

keep DateToCheck;

output;

end;

/* Cartesian join to get a list of months and company names */

proc sql noprint;

create table AllMonthsNames as

select n.CompanyName, a.DateToCheck from Names n cross join AllMonths a;

quit;

/* Join the two, to get a full list of months with Type */

proc sql noprint;

create table Want as

select h.*, a.DateToCheck from Have h right join AllMonthsNames a on(h.TestDate = a.DateToCheck and h.CompanyName = a.CompanyName)

order by a.CompanyName, a.DateToCheck;

quit;

/* Ripple the values of CompanyName and Type */

data Want;

set Want;

length OldCompanyName $8 OldType $1 OldDate 8;

retain OldCompanyName OldType OldDate;

if _n_ = 1 then

OldDate = DateToCheck;

if DateToCheck < OldDate /* We've started on a new company */

then do;

OldDate = DateToCheck;

call missing(OldType, OldCompanyName);

OldDate = DateToCheck;

end;

else OldDate = DateToCheck;

if ^missing(Type) then

OldType = Type;

else Type = OldType;

if ^missing(CompanyName) then

OldCompanyName = CompanyName;

else CompanyName = OldCompanyName;

drop OldCompanyName OldType OldDate TestDate;

run;

Re: data generation

novinosrin — Thu, 20 Apr 2017 17:17:34 GMT

Thank you for your acknowledgement. It 's almost 11:00pm at Chennai and I have had a few beers this evening and feeling so tired and knackered. I will look into your requirement and properly modify the code tomorrow at least for the sake of sharing knowledge. Meanwhile, I hope @TomKari 's code is useful for you. He is a genius and I have paid attention to many of his posts since I watched his interview on youtube few years ago.

Regards,

Naveen Srinivasan

Re: data generation

novinosrin — Fri, 21 Apr 2017 15:38:01 GMT

@Kyojik Hi, Sorry for not being able to give you the much needed solution last night as I was awefully tired. Here you go, the below will work for multiple companies. Please do let me know should if it doesn't quite meet your requirement. It's actually easy. So feel free to reach out.

Regards,

Naveen Srinivasan

proc sort data=have;

by companyname year month;

run;

data want(rename=(_month=month));

do until(last.companyname);

do until(last.year);

set have;

by companyname year month;

length _type __type $1;

if first.companyname or first.year then call missing(_month,_type,__type);

if first.year and last.year and month=12 then

do;

_month=month;

output;

end;

else if (first.year and last.year) and month<12 then

do;

do _month=month to 12;

output;

end;

else if (first.year and not last.year) and month<12 then

do;

_month=month;

_type=type;

end;

else if not first.year then

do;

__type=type;

do _month=_month to month-1;

type=_type;

output;

end;

_type=__type;

if last.year then do;

do _month=month to 12;

type=_type;

output;

end;

drop month _type __type;

run;