topic Re: Adding observation value to another observation in SAS Programming

Adding observation value to another observation

polpel — Thu, 24 Oct 2019 13:37:56 GMT

Hello,

As explicit as my problem may sound haha, it's been really annoying as I'm new to SAS.

Basically I have a dataset with different types of observations (call them "main" and "sub"), then I have the amount it costs, the transaction id and sub-id.

So for example, we've got:

type cost id sub-id

main 10 123 1

sub 5 123 2

What I want is this

type cost id sub-id sub-cost

main 10 123 1 5

Note that there can be more than one pair of main/sub for one id... and sometimes mains are alone but subs are always with a main...

Thnkassss

Re: Adding observation value to another observation

PaigeMiller — Thu, 24 Oct 2019 14:40:56 GMT

It's nearly impossible to provide a generalized solution for you that will work in many situations, as you have provided an example that is really only one situation. If the entire data set mirrors this one example (although I suspect it won't), then this will work:

data want;
    merge have(where=(type='main')) have(where=(type='sub')
       rename=(cost=sub_cost));
run;

Also please note that SAS variables cannot have a hyphen in their name, even though your example seems to imply they can have hyphens.

Re: Adding observation value to another observation

polpel — Fri, 25 Oct 2019 09:48:17 GMT

Hello,

I've written in SAS code the have and want datasets, do you think you could help with the code that would go in between both pls?

data have;
	input type $ cost key sub_key;
	cards;
main 15 144 1
sub 13 144 2
main 10 144 3
sub 12 144 4
main 21 129 1
;
run;

data want;
	input type $ cost sub_cost key sub_key;
	cards;
main 15 13 144 1
main 10 12 144 3
main 21 0 129 1
;
run;

Re: Adding observation value to another observation

andreas_lds — Fri, 25 Oct 2019 10:26:45 GMT

Have you tried the solution provided by @PaigeMiller ? Afaik it will create the expected dataset - maybe the variables are in ordered differently, but the order of variables is hardly important at all.

Re: Adding observation value to another observation

PaigeMiller — Fri, 25 Oct 2019 11:56:22 GMT

This data contains examples that my original code will not properly handle, and this data probably should have been provided originally.

Since there is no obvious ID variable to merge on, and some "main" records are not followed by "sub" records, I don't have an obvious answer. Also, this remains a very brief example, and so before trying to think about a possible solution, I'd like to know the full set of rules you want to follow.Specifically (but not limited to)

are there cases where "main" has two (or more) "sub records"? (And what should be done in that case)
are there cases where "main" is followed by another "main"?
do variables cost and key play any role here?

Re: Adding observation value to another observation

polpel — Fri, 25 Oct 2019 12:53:39 GMT

hello again,

yes i'm sorry, I tried @PaigeMiller 's code and it works, thanks a lot!!

Re: Adding observation value to another observation

polpel — Fri, 25 Oct 2019 13:11:44 GMT

Hello Again!

To answer your request, the rules are that:

- a main can be solo (one observation for the id and no sub)

- a main can have only one sub and the sub-id of the sub is the main's sub-id + 1

- there can be two or more mains per id, but each individual one does'nt have a sub

- subs can't be alone

Hope that was clear haha

Re: Adding observation value to another observation

Tom — Fri, 25 Oct 2019 13:23:20 GMT

It looks like perhaps you are looking at the problem backwards. You don't want move the SUB values UP to the MAIN line. You want to keep the MAIN values and CARRY them FORWARD onto the SUB lines.

data have;
  input type $ cost id sub_id ;
cards;
main 10 123 1
sub 5 123 2
;
data want;
  set have ;
  if type='main' then do;
    main_cost=cost;
    main_id = id;
  end;
  else output;
  retain main_cost main_id ;
  rename cost=sub_cost ;
  drop type;
run;

proc print;
run;

Re: Adding observation value to another observation

polpel — Fri, 25 Oct 2019 15:24:32 GMT

Hello and thanks for the answer,

Would this work if there are multiple main/sub for a single id?

Good day

Re: Adding observation value to another observation

polpel — Mon, 28 Oct 2019 10:39:48 GMT

Hello,
This method seems to work only with mains that are paired with a main, which means that all mains who don't have a sub are "dropped"...