Adding observation value to another observation

[polpel]

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

[PaigeMiller]

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.

[polpel]

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;

[andreas_lds]

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.

[PaigeMiller]

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?

[polpel]

hello again,

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

[polpel]

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

[Tom]

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;

[polpel]

Hello and thanks for the answer,

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

Good day

[polpel]

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"...