advanced expression combining household data

[Tglass55]

Version SAS Enterprise Guide 4.3

Attempting to compute new column showing household data for individuals as part of a project using Census data.

I am using the "query builder function"

select "computed columns"

select "new"

select "advanced expression"

where it brings up "enter an expression"

Each row is a different individual. t1.serialno is the family code, t1.wagp is the individual's wages for the year I am using. Complementary relationship (RELP) is calculated as follows:

CASE

WHEN 0 = t1.RELP THEN 1

WHEN 13 = t1.RELP THEN 1

WHEN 6 = t1.RELP THEN 1

WHEN t1.RELP >= 14 AND t1.RELP <= 17 THEN 0

WHEN t1.RELP >= 2 AND t1.RELP <= 5 THEN 0

WHEN t1.RELP >= 7 AND t1.RELP <= 12 THEN 0

ELSE t1.RELP

END)

I am looking to find the sum of the wages of all those with a certain family code for each individual in that family who holds a certain relationship to the person who filled out the survey. I am trying to create a "household income" column.   see below

An example

t1.serialno	t1.wagp	Complementary Relationship	"Household Income"
1	20	1	30
1	40	0	30
1	10	1	30
2	30	1	30
2	15	0	30
2	0	1	30
3	35	0	20
3	10	1	20
3	10	1	20

I am brand new to SAS and any help would be great!  Thanks!

Tom

[RW9]

Hi,

Well, I don't use EG or this query builder, however it should be straightforward in SQL, something (and you question was a bit vague) along the lines of:

select     A.*,

               B.TOTAL

from       HAVE

left join (select T1_SERIAL_NO,sum(HOUSEHOLD_INCOME) from HAVE group by T1_SERIAL_NO) B

on          A.T1_SERIAL_NO=B.T1_SERIAL_NO

[Tglass55]

Sorry.  What was confusing about my my question?

I do have some questions:

Would I just type in this code into the "advanced expression" section?

Is the * next to A a part of the equation or does it signify something else?

What is "have?"

Is it the final expression "on" which creates the new column?

thanks again.  I apologize if these questions are rudementary.

[stat_sas]

Hi,

Try this.

data have;
input T1_SERIAL_NO T1_Wagp Relationship;
datalines;
1 20 1
1 40 0
1 10 1
2 30 1
2 15 0
2 0 1
3 35 0
3 10 1
3 10 1
;

proc sql;
select *,sum(case when Relationship=1 then T1_Wagp end) as household_income from have
group by T1_SERIAL_NO;
quit;

[Tglass55]

for the data lines portion, would I need to put each in individually because I have several million data points.

is the * next to select a part of the equation or is there somehting else that is supposed to go there?

Would I put this into the "advanced expression" area? 

[stat_sas]

Hi,

I think you are talking about EG, I am sorry, this is just syntax not sure how to put this into EG.

[Scott_Mitchell]

I don't use EG very frequently, but thought this might be what you are looking to do.

Please see attached project.  The input dataset is called HAVE.

Let me know if the attached causes you any trouble.

[Tglass55]

Error opening data "HAVE".

I tried saving the file and then re-opening it but it still didn't work.   any advice?

[Tglass55]

I put in the portion of the data set I'm working with on this thread above.  Can you maybe use that with the same equation so I can take a look?

thanks

[Scott_Mitchell · Accepted solution]

Sorry TGlass I have been away.

Here is a version that uses your dataset name. I have also included the code that creates the test data I used.

I can't see the variable names as you provided the EG Project File but not the .SAS7bdat file.

Let me know if you have any issues.

Regards,

Scott

[Tglass55]

This was most helpful!  I took me a minute to understand, this is the closest I've gotten, seriously thank you! 

There is an issue with it bringing over every row though.  I'm not sure what the problem is, it may be with the Table join.  I've tried working on this thing for hours and I can't get it.