BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
Tglass55
Calcite | Level 5

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

1 ACCEPTED SOLUTION

Accepted Solutions
Scott_Mitchell
Quartz | Level 8

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

10 REPLIES 10
RW9
Diamond | Level 26 RW9
Diamond | Level 26

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
Calcite | Level 5

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
Ammonite | Level 13

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
Calcite | Level 5

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
Ammonite | Level 13

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
Quartz | Level 8

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
Calcite | Level 5

Error opening data "HAVE".

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

Tglass55
Calcite | Level 5

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
Quartz | Level 8

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
Calcite | Level 5

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. 

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

Multiple Linear Regression in SAS

Learn how to run multiple linear regression models with and without interactions, presented by SAS user Alex Chaplin.

Find more tutorials on the SAS Users YouTube channel.

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