topic Re: LOCF in SAS Programming

LOCF

Wafflecakes — Tue, 12 Sep 2017 21:26:34 GMT

Greetings,

I have the following dataset:

HAVE

id month sales sales2

1 1 1200 500

1 2 . .

1 3 1500 .

1 4 . .

2 1 . .

2 2 1000 300

2 3 . .

2 4 . .

WANT

id month sales sales2

1 1 1200 500

1 2 1200 500

1 3 1500 500

1 4 1500 500

2 1 . .

2 2 1000 300

2 3 1000 300

2 4 1000 300

How would I go about coding this? Ideally I want the missing for id=2 to be filled in as well, but does that violate LOCF?

Re: LOCF

novinosrin — Tue, 12 Sep 2017 20:23:58 GMT

data have;
input id sales;
datalines;
1 1200
1 .
1 1500
1 .
2 .
2 1000
2 .
2 .
2 .
2 .
2 3000
3 .
3 .
3 1100
3 .
3 .
;

data want;
set have;
by id;
retain _sales;
if first.id then call missing(_sales);
if sales then _sales=sales;
drop sales;
rename _sales=sales;
run;

Re: LOCF

art297 — Tue, 12 Sep 2017 20:28:45 GMT

If you want to accomplish it directly during input, use a lag for id. e.g.:

data HAVE (drop=hold);
  input id month sales;
  retain hold;
  if id ne lag(id) then hold=sales;
  else if not missing(sales) then hold=sales;
  else sales=hold;
  cards;
1   1   1200
1   2   .
1   3   1500
1   4   .
2   1   . 
2   2   1000
2   3   .
2   4   .
2   5   .
2   6   .
2   7   3000
3   1   .
3   2   .
3   3   1100
3   4   .
3   5   .
;

Art, CEO, AnalystFinder.com

Re: LOCF

Wafflecakes — Tue, 12 Sep 2017 20:31:12 GMT

I think that does the trick!

How does that differ from this code that I used?

data want;

set have;

by id;

retain _sales;

if first.id then _sales=.;

if sales~=. then sales=_sales;

if last.id;

run;

What is the purpose of the 'call missing' and retain keywords?

Re: LOCF

novinosrin — Tue, 12 Sep 2017 20:33:31 GMT

Call missing: To avoid retaining the value from the previous by group

And retain: To hold the value across obs coz values created by assignment statement are reset to missing during each interation of the datastep

Re: LOCF

Wafflecakes — Tue, 12 Sep 2017 20:32:59 GMT

so in essence it is the same as 'if first.id then_sales=.;' that I used?

Re: LOCF

Wafflecakes — Tue, 12 Sep 2017 20:37:12 GMT

Thanks both for the quick replies. Would it be possible to do this for multiple variables at a time or does it have to be done individually?

Re: LOCF

novinosrin — Tue, 12 Sep 2017 20:38:22 GMT

Sorry, what do you mean? Can you please clarify. I am little too slow to read between the lines

Re: LOCF

Wafflecakes — Tue, 12 Sep 2017 20:40:48 GMT

Basically I was saying that your call missing line is equivalent to 'if first.id then _sales=.;

I also do not understand why when I put 'if last.id;' that, that removes all of my observations for each id and leaves only one observation.

I was also wondering if you can do this same process for multiple variables at a time instead of individually?

Re: LOCF

novinosrin — Tue, 12 Sep 2017 20:42:58 GMT

Yes sorry. Your -call missing line is equivalent to 'if first.id then _sales=.; is right and absolute

also do not understand why when I put 'if last.id;' that, that removes all of my observations for each id and leaves only one observation.-- You don't need this subsetting if as you want to process and keep all obs

I was also wondering if you can do this same process for multiple variables at a time instead of individually?-- Yes, I think so if i understand you well enough.

Re: LOCF

Wafflecakes — Tue, 12 Sep 2017 20:45:20 GMT

How would I do it for multiple variables at a time? Can you give eaxample?

Re: LOCF

novinosrin — Tue, 12 Sep 2017 20:50:27 GMT

Ok, if i understand what you mean, you may have multiple by groups. This could be a cascading or like a parent child group-sub group relationship. You just process by groups using first and last automatic vars from the child to the outer most by group.

I am too lazy to write a program to show you a working example. May i request you to modify your req with what you have in mind in the question and I can work on the solution

I do apologise

Re: LOCF

novinosrin — Tue, 12 Sep 2017 20:57:03 GMT

Still no change sir:

data have;

input id month sales;

datalines;

1 1 1200

1 2 .

1 3 1500

1 4 .

2 1 .

2 2 1000

2 3 .

2 4 .

2 5 .

2 6 .

2 7 3000

3 1 .

3 2 .

3 3 1100

3 4 .

3 5 .

;

data want;

set have;

by id;

retain _sales;

if first.id then call missing(_sales);

if sales then _sales=sales;

drop sales;

rename _sales=sales;

run;

Re: LOCF

Wafflecakes — Tue, 12 Sep 2017 20:59:41 GMT

I edited the original.

Re: LOCF

novinosrin — Tue, 12 Sep 2017 21:03:30 GMT

Still exactly the same logic:

data have;

input id month sales sales2;

datalines;

1 1 1200 500

1 2 . .

1 3 1500 .

1 4 . .

2 1 . .

2 2 1000 300

2 3 . .

2 4 . .

;

data want;

set have;

by id;

retain _sales __sales;

if first.id then call missing(_sales,__sales);

if sales then _sales=sales;

if sales2 then __sales=sales2;

drop sales sales2;

rename _sales=sales __sales=sales2;

run;

Re: LOCF

Wafflecakes — Tue, 12 Sep 2017 21:04:56 GMT

And basically that logic can be applied to an 'infinite' number of variables in the same datastep?

Re: LOCF

novinosrin — Tue, 12 Sep 2017 21:10:10 GMT

Well if it's too many(variables), then lazy people like me would have to find a way to shorten the typing. You would have noticed I had to type in another variable.Then it goes to arrays, hashes etc. The fun about sas is that harder the concept as it may seem (eg hashes) the easier is to process 🙂 Have fun! I am leaving for my lectures at the university.

Feel to free to open a new thread with a new question. Cheers!

Re: LOCF

Wafflecakes — Tue, 12 Sep 2017 21:51:28 GMT

HAVE

id month sales

1 1 1200

1 2 .

1 3 1500

1 4 .

2 1 1000

2 2 1000

2 3 .

2 4 .

WANT

id month sales

1 1 1200

1 2 1200

1 3 1500

1 4 1500

2 1 . <<<<<<<<<<<<<<<

2 2 1000

2 3 1000

2 4 1000

Novo - the code that you suggested seems to work for the most part. It looks like theres an error though because sales2/id 2 is supposed to be 1000, but it shows up as missing?

Re: LOCF

novinosrin — Tue, 12 Sep 2017 21:33:02 GMT

still not a single change in my code yet again:

data have;
input id month sales sales2;
datalines;
1 1 1200 500
1 2 . .
1 3 1500 .
1 4 . .
2 1 1000 1000
2 2 1000 300
2 3 . .
2 4 . .
;

data want;
set have;
by id;
retain _sales __sales;
if first.id then call missing(_sales,__sales);
if sales then _sales=sales;
if sales2 then __sales=sales2;
drop sales sales2;
rename _sales=sales __sales=sales2;
run;

Re: LOCF

Wafflecakes — Tue, 12 Sep 2017 21:52:04 GMT

Sorry forget about sales 2. Look at the previous post again. If you notice, observation 2 month 1 is supposed to be 1000, but it is set to missing.