topic Re: advanced expression combining household data in SAS Forecasting and Econometrics

advanced expression combining household data

Tglass55 — Sun, 27 Jul 2014 22:34:40 GMT

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

Re: advanced expression combining household data

RW9 — Mon, 28 Jul 2014 08:18:48 GMT

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

Re: advanced expression combining household data

Scott_Mitchell — Mon, 28 Jul 2014 08:32:47 GMT

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.

Re: advanced expression combining household data

Tglass55 — Wed, 06 Aug 2014 15:24:17 GMT

Error opening data "HAVE".

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

Re: advanced expression combining household data

Tglass55 — Wed, 06 Aug 2014 15:35:50 GMT

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.

Re: advanced expression combining household data

stat_sas — Wed, 06 Aug 2014 15:43:49 GMT

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;

Re: advanced expression combining household data

Tglass55 — Wed, 06 Aug 2014 16:01:35 GMT

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?

Re: advanced expression combining household data

stat_sas — Thu, 07 Aug 2014 00:16:21 GMT

Hi,

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

Re: advanced expression combining household data

Tglass55 — Thu, 07 Aug 2014 01:58:06 GMT

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

Re: advanced expression combining household data

Scott_Mitchell — Sun, 10 Aug 2014 06:24:07 GMT

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

Re: advanced expression combining household data

Tglass55 — Fri, 15 Aug 2014 04:18:28 GMT

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.