Forecasting using SAS Forecast Server, SAS/ETS, and more

advanced expression combining household data

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 11
Accepted Solution

advanced expression combining household data

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.serialnot1.wagpComplementary Relationship"Household Income"
120130
140030
110130
230130
215030
20130
335020
310120
310120

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

Tom

Attachment

Accepted Solutions
Solution
‎08-10-2014 02:24 AM
Super Contributor
Posts: 297

Re: advanced expression combining household data

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

View solution in original post

Attachment

All Replies
Super User
Super User
Posts: 7,942

Re: advanced expression combining household data

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

Occasional Contributor
Posts: 11

Re: advanced expression combining household data

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.

Trusted Advisor
Posts: 1,228

Re: advanced expression combining household data

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;

Occasional Contributor
Posts: 11

Re: advanced expression combining household data

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? 

Trusted Advisor
Posts: 1,228

Re: advanced expression combining household data

Hi,

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

Super Contributor
Posts: 297

Re: advanced expression combining household data

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.

Attachment
Occasional Contributor
Posts: 11

Re: advanced expression combining household data

Posted in reply to Scott_Mitchell

Error opening data "HAVE".

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

Occasional Contributor
Posts: 11

Re: advanced expression combining household data

Posted in reply to Scott_Mitchell

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

Solution
‎08-10-2014 02:24 AM
Super Contributor
Posts: 297

Re: advanced expression combining household data

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

Attachment
Occasional Contributor
Posts: 11

Re: advanced expression combining household data

Posted in reply to Scott_Mitchell

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. 

Attachment
🔒 This topic is solved and locked.

Need further help from the community? Please ask a new question.

Discussion stats
  • 10 replies
  • 1031 views
  • 0 likes
  • 4 in conversation