264.TEMPLES OF INDIA DATASET ANALYSIS USING PROC PRINT | PROC CONTENTS | PROC SORT | PROC FREQ | PROC MEANS | PROC SQL | PROC TABULATE | PROC REPORT | PROC TRANSPOSE | PROC FORMAT | PROC RANK | PROC SGPLOT | MACROS

on
0

262.FAMILY RELATIONS IN INDIA — DATA CREATION | PROC FORMAT | PROC CONTENTS | PROC PRINT | PROC FREQ | PROC SORT | PROC MEANS | PROC SUMMARY | PROC SQL | PROC TRANSPOSE | PROC TABULATE | PROC REPORT | PROC SGPLOT | MACROS

on

FAMILY RELATIONS IN INDIA — DATA CREATION | PROC FORMAT | PROC CONTENTS | PROC PRINT | PROC FREQ | PROC SORT | PROC MEANS | PROC SUMMARY | PROC SQL | PROC TRANSPOSE | PROC TABULATE | PROC REPORT | PROC SGPLOT | MACROS

 /*Creaing a family with different types of relations dataset*/

options nocenter nodate nonumber formdlim='-' ls=120 ps=60;

1. Formats 

proc format;

  value $gender  'M'='Male' 'F'='Female';

  value $marstat 'Single'='Single' 'Married'='Married' 'Widowed'='Widowed' 'Divorced'='Divorced';

  value $edu     'School'='School' 'Diploma'='Diploma' 'UG'='Undergraduate'

                 'PG'='Postgraduate' 'PhD'='Doctorate';

  value $relhd   'Head'='Head' 'Spouse'='Spouse' 'Son'='Son' 'Daughter'='Daughter'

                 'Father'='Father' 'Mother'='Mother' 'Brother'='Brother' 'Sister'='Sister'

                 'Uncle'='Uncle' 'Aunt'='Aunt' 'Nephew'='Nephew' 'Niece'='Niece'

                 'Cousin'='Cousin' 'Grandfather'='Grandfather' 'Grandmother'='Grandmother';

run;


2. Core PERSON table with 28 persons across 3 households 

options nocenter;

data family_persons;

  length Household_ID $6 Person_ID $6 Name $20 Gender $1 Relation_To_Head $12

         Marital_Status $8 Education $12 Occupation $20 City $20;

  format Gender $gender. Marital_Status $marstat. Education $edu.

         Relation_To_Head $relhd. Birth_Date date9.;

  infile datalines dlm='|' dsd truncover;

  input Household_ID $ Person_ID $ Name $ Gender $ Relation_To_Head $ 

        Marital_Status $ Education $ Occupation $ City $ Birth_Date :date9. Income Spouse_ID $;

  datalines;

HH101|P101|Ramesh_Patel|M|Head|Married|UG|Accountant|Ahmedabad|14MAY1978|850000|P102

HH101|P102|Meera_Patel|F|Spouse|Married|PG|Teacher|Ahmedabad|22AUG1980|720000|P101

HH101|P103|Arjun_Patel|M|Son|Single|UG|Engineer_Trainee|Ahmedabad|10JAN2001|420000|

HH101|P104|Anya_Patel|F|Daughter|Single|UG|Student|Ahmedabad|05JUL2003|0|

HH101|P105|Nishit_Patel|M|Father|Married|Diploma|Retired_Foreman|Ahmedabad|09MAR1950|300000|P106

HH101|P106|Bhavna_Patel|F|Mother|Married|School|Homemaker|Ahmedabad|18DEC1953|0|P105

HH101|P107|Vikas_Patel|M|Brother|Married|UG|Sales_Exec|Ahmedabad|30SEP1983|600000|P108

HH101|P108|Kavya_Patel|F|Aunt|Married|UG|Boutique_Owner|Ahmedabad|12FEB1986|550000|P107

HH101|P109|Ria_Patel|F|Niece|Single|School|Student|Ahmedabad|25NOV2011|0|

HH101|P110|Ishaan_Patel|M|Nephew|Single|School|Student|Ahmedabad|14APR2014|0|

HH101|P111|Aarav_Patel|M|Cousin|Single|UG|Intern|Ahmedabad|02MAY2000|300000|

HH101|P112|Disha_Patel|F|Cousin|Single|PG|MCA_Student|Ahmedabad|28OCT1999|0|


HH102|P201|Sridhar_Reddy|M|Head|Married|PG|Software_Manager|Hyderabad|03JUN1982|1400000|P202

HH102|P202|Lakshmi_Reddy|F|Spouse|Married|UG|HR_Exec|Hyderabad|17JAN1985|900000|P201

HH102|P203|Teja_Reddy|M|Son|Single|School|Student|Hyderabad|09SEP2012|0|

HH102|P204|Maya_Reddy|F|Daughter|Single|School|Student|Hyderabad|22FEB2015|0|

HH102|P205|Raghav_Reddy|M|Father|Widowed|Diploma|Retired_Technician|Hyderabad|01APR1956|280000|

HH102|P206|Saraswati_Reddy|F|Mother|Widowed|School|Homemaker|Hyderabad|29JUL1958|0|

HH102|P207|Nikhil_Reddy|M|Brother|Married|UG|Bank_Officer|Hyderabad|05MAY1987|950000|P208

HH102|P208|Anita_Reddy|F|Aunt|Married|UG|Pharma_Rep|Hyderabad|26DEC1989|700000|P207

HH102|P209|Rohit_Reddy|M|Nephew|Single|School|Student|Hyderabad|11AUG2017|0|

HH102|P210|Sneha_Reddy|F|Niece|Single|School|Student|Hyderabad|02JAN2020|0|


HH103|P301|Vikram_Sharma|M|Head|Married|PhD|Professor|Pune|14FEB1975|1600000|P302

HH103|P302|Priya_Sharma|F|Spouse|Married|PG|Architect|Pune|19MAR1978|1200000|P301

HH103|P303|Kabir_Sharma|M|Son|Single|UG|Graphic_Designer|Pune|23JUL1999|650000|

HH103|P304|Naina_Sharma|F|Daughter|Single|PG|Data_Analyst|Pune|12DEC2001|800000|

HH103|P305|Devika_Iyer|F|Cousin|Divorced|UG|Journalist|Pune|30OCT1984|700000|

HH103|P306|Harish_Iyer|M|Uncle|Married|UG|Shop_Owner|Pune|07JAN1968|550000|P307

HH103|P307|Latha_Iyer|F|Aunt|Married|School|Homemaker|Pune|22MAY1971|0|P306

;

run;

proc print;run;

Output:

ObsHousehold_IDPerson_IDNameGenderRelation_To_HeadMarital_StatusEducationOccupationCityBirth_DateIncomeSpouse_ID
1HH101P101Ramesh_PatelMaleHeadMarriedUndergraduateAccountantAhmedabad14MAY1978850000P102
2HH101P102Meera_PatelFemaleSpouseMarriedPostgraduateTeacherAhmedabad22AUG1980720000P101
3HH101P103Arjun_PatelMaleSonSingleUndergraduateEngineer_TraineeAhmedabad10JAN2001420000 
4HH101P104Anya_PatelFemaleDaughterSingleUndergraduateStudentAhmedabad05JUL20030 
5HH101P105Nishit_PatelMaleFatherMarriedDiplomaRetired_ForemanAhmedabad09MAR1950300000P106
6HH101P106Bhavna_PatelFemaleMotherMarriedSchoolHomemakerAhmedabad18DEC19530P105
7HH101P107Vikas_PatelMaleBrotherMarriedUndergraduateSales_ExecAhmedabad30SEP1983600000P108
8HH101P108Kavya_PatelFemaleAuntMarriedUndergraduateBoutique_OwnerAhmedabad12FEB1986550000P107
9HH101P109Ria_PatelFemaleNieceSingleSchoolStudentAhmedabad25NOV20110 
10HH101P110Ishaan_PatelMaleNephewSingleSchoolStudentAhmedabad14APR20140 
11HH101P111Aarav_PatelMaleCousinSingleUndergraduateInternAhmedabad02MAY2000300000 
12HH101P112Disha_PatelFemaleCousinSinglePostgraduateMCA_StudentAhmedabad28OCT19990 
13         .. 
14HH102P201Sridhar_ReddyMaleHeadMarriedPostgraduateSoftware_ManagerHyderabad03JUN19821400000P202
15HH102P202Lakshmi_ReddyFemaleSpouseMarriedUndergraduateHR_ExecHyderabad17JAN1985900000P201
16HH102P203Teja_ReddyMaleSonSingleSchoolStudentHyderabad09SEP20120 
17HH102P204Maya_ReddyFemaleDaughterSingleSchoolStudentHyderabad22FEB20150 
18HH102P205Raghav_ReddyMaleFatherWidowedDiplomaRetired_TechnicianHyderabad01APR1956280000 
19HH102P206Saraswati_ReddyFemaleMotherWidowedSchoolHomemakerHyderabad29JUL19580 
20HH102P207Nikhil_ReddyMaleBrotherMarriedUndergraduateBank_OfficerHyderabad05MAY1987950000P208
21HH102P208Anita_ReddyFemaleAuntMarriedUndergraduatePharma_RepHyderabad26DEC1989700000P207
22HH102P209Rohit_ReddyMaleNephewSingleSchoolStudentHyderabad11AUG20170 
23HH102P210Sneha_ReddyFemaleNieceSingleSchoolStudentHyderabad02JAN20200 
24         .. 
25HH103P301Vikram_SharmaMaleHeadMarriedDoctorateProfessorPune14FEB19751600000P302
26HH103P302Priya_SharmaFemaleSpouseMarriedPostgraduateArchitectPune19MAR19781200000P301
27HH103P303Kabir_SharmaMaleSonSingleUndergraduateGraphic_DesignerPune23JUL1999650000 
28HH103P304Naina_SharmaFemaleDaughterSinglePostgraduateData_AnalystPune12DEC2001800000 
29HH103P305Devika_IyerFemaleCousinDivorcedUndergraduateJournalistPune30OCT1984700000 
30HH103P306Harish_IyerMaleUncleMarriedUndergraduateShop_OwnerPune07JAN1968550000P307
31HH103P307Latha_IyerFemaleAuntMarriedSchoolHomemakerPune22MAY19710P306


3. Structure check 

proc contents data=family_persons varnum; run;

Output:

The CONTENTS Procedure

Data Set NameWORK.FAMILY_PERSONSObservations31
Member TypeDATAVariables12
EngineV9Indexes0
Created08/16/2025 19:43:23Observation Length136
Last Modified08/16/2025 19:43:23Deleted Observations0
Protection CompressedNO
Data Set Type SortedNO
Label   
Data RepresentationSOLARIS_X86_64, LINUX_X86_64, ALPHA_TRU64, LINUX_IA64  
Encodingutf-8 Unicode (UTF-8)  
Engine/Host Dependent Information
Data Set Page Size131072
Number of Data Set Pages1
First Data Page1
Max Obs per Page962
Obs in First Data Page31
Number of Data Set Repairs0
Filename/saswork/SAS_work1AA000005216_odaws02-apse1-2.oda.sas.com/SAS_work38DE00005216_odaws02-apse1-2.oda.sas.com/family_persons.sas7bdat
Release Created9.0401M8
Host CreatedLinux
Inode Number201328344
Access Permissionrw-r--r--
Owner Nameu63247146
File Size256KB
File Size (bytes)262144
Variables in Creation Order
#VariableTypeLenFormat
1Household_IDChar6 
2Person_IDChar6 
3NameChar20 
4GenderChar1$GENDER.
5Relation_To_HeadChar12$RELHD.
6Marital_StatusChar8$MARSTAT.
7EducationChar12$EDU.
8OccupationChar20 
9CityChar20 
10Birth_DateNum8DATE9.
11IncomeNum8 
12Spouse_IDChar8 

 Derived variables 

data family_enriched;

  set family_persons;

  Age = intck('year', Birth_Date, today(), 'C');

  length Age_Group $12 Income_Band $12;

  if Age < 18 then Age_Group='Child';

  else if 18 <= Age < 30 then Age_Group='Youth';

  else if 30 <= Age < 60 then Age_Group='Adult';

  else Age_Group='Senior';


  if Income < 100000 then Income_Band='Nil/Low';

  else if Income < 500000 then Income_Band='Lower-Mid';

  else if Income < 900000 then Income_Band='Mid';

  else if Income < 1300000 then Income_Band='Upper-Mid';

  else Income_Band='High';

run;

proc print;run;

Output:

ObsHousehold_IDPerson_IDNameGenderRelation_To_HeadMarital_StatusEducationOccupationCityBirth_DateIncomeSpouse_IDAgeAge_GroupIncome_Band
1HH101P101Ramesh_PatelMaleHeadMarriedUndergraduateAccountantAhmedabad14MAY1978850000P10247AdultMid
2HH101P102Meera_PatelFemaleSpouseMarriedPostgraduateTeacherAhmedabad22AUG1980720000P10144AdultMid
3HH101P103Arjun_PatelMaleSonSingleUndergraduateEngineer_TraineeAhmedabad10JAN2001420000 24YouthLower-Mid
4HH101P104Anya_PatelFemaleDaughterSingleUndergraduateStudentAhmedabad05JUL20030 22YouthNil/Low
5HH101P105Nishit_PatelMaleFatherMarriedDiplomaRetired_ForemanAhmedabad09MAR1950300000P10675SeniorLower-Mid
6HH101P106Bhavna_PatelFemaleMotherMarriedSchoolHomemakerAhmedabad18DEC19530P10571SeniorNil/Low
7HH101P107Vikas_PatelMaleBrotherMarriedUndergraduateSales_ExecAhmedabad30SEP1983600000P10841AdultMid
8HH101P108Kavya_PatelFemaleAuntMarriedUndergraduateBoutique_OwnerAhmedabad12FEB1986550000P10739AdultMid
9HH101P109Ria_PatelFemaleNieceSingleSchoolStudentAhmedabad25NOV20110 13ChildNil/Low
10HH101P110Ishaan_PatelMaleNephewSingleSchoolStudentAhmedabad14APR20140 11ChildNil/Low
11HH101P111Aarav_PatelMaleCousinSingleUndergraduateInternAhmedabad02MAY2000300000 25YouthLower-Mid
12HH101P112Disha_PatelFemaleCousinSinglePostgraduateMCA_StudentAhmedabad28OCT19990 25YouthNil/Low
13         .. .ChildNil/Low
14HH102P201Sridhar_ReddyMaleHeadMarriedPostgraduateSoftware_ManagerHyderabad03JUN19821400000P20243AdultHigh
15HH102P202Lakshmi_ReddyFemaleSpouseMarriedUndergraduateHR_ExecHyderabad17JAN1985900000P20140AdultUpper-Mid
16HH102P203Teja_ReddyMaleSonSingleSchoolStudentHyderabad09SEP20120 12ChildNil/Low
17HH102P204Maya_ReddyFemaleDaughterSingleSchoolStudentHyderabad22FEB20150 10ChildNil/Low
18HH102P205Raghav_ReddyMaleFatherWidowedDiplomaRetired_TechnicianHyderabad01APR1956280000 69SeniorLower-Mid
19HH102P206Saraswati_ReddyFemaleMotherWidowedSchoolHomemakerHyderabad29JUL19580 67SeniorNil/Low
20HH102P207Nikhil_ReddyMaleBrotherMarriedUndergraduateBank_OfficerHyderabad05MAY1987950000P20838AdultUpper-Mid
21HH102P208Anita_ReddyFemaleAuntMarriedUndergraduatePharma_RepHyderabad26DEC1989700000P20735AdultMid
22HH102P209Rohit_ReddyMaleNephewSingleSchoolStudentHyderabad11AUG20170 8ChildNil/Low
23HH102P210Sneha_ReddyFemaleNieceSingleSchoolStudentHyderabad02JAN20200 5ChildNil/Low
24         .. .ChildNil/Low
25HH103P301Vikram_SharmaMaleHeadMarriedDoctorateProfessorPune14FEB19751600000P30250AdultHigh
26HH103P302Priya_SharmaFemaleSpouseMarriedPostgraduateArchitectPune19MAR19781200000P30147AdultUpper-Mid
27HH103P303Kabir_SharmaMaleSonSingleUndergraduateGraphic_DesignerPune23JUL1999650000 26YouthMid
28HH103P304Naina_SharmaFemaleDaughterSinglePostgraduateData_AnalystPune12DEC2001800000 23YouthMid
29HH103P305Devika_IyerFemaleCousinDivorcedUndergraduateJournalistPune30OCT1984700000 40AdultMid
30HH103P306Harish_IyerMaleUncleMarriedUndergraduateShop_OwnerPune07JAN1968550000P30757AdultMid
31HH103P307Latha_IyerFemaleAuntMarriedSchoolHomemakerPune22MAY19710P30654AdultNil/Low


4. Relationship edges 

data family_rel;

  length Person1 $6 Person2 $6 Rel_Type $10;

  infile datalines dlm='|' dsd truncover;

  input Person1 $ Person2 $ Rel_Type $;

  datalines;

P101|P103|parent

P101|P104|parent

P102|P103|parent

P102|P104|parent

P107|P109|parent

P108|P109|parent

P107|P110|parent

P108|P110|parent

P201|P203|parent

P201|P204|parent

P202|P203|parent

P202|P204|parent

P207|P209|parent

P208|P209|parent

P207|P210|parent

P208|P210|parent

P101|P102|spouse

P107|P108|spouse

P201|P202|spouse

P207|P208|spouse

P301|P302|spouse

P306|P307|spouse

P103|P104|sibling

P109|P110|sibling

P203|P204|sibling

P209|P210|sibling

P303|P304|sibling

P111|P203|cousin

P303|P103|cousin

;

run;

proc print;run;

Output:

ObsPerson1Person2Rel_Type
1P101P103parent
2P101P104parent
3P102P103parent
4P102P104parent
5P107P109parent
6P108P109parent
7P107P110parent
8P108P110parent
9P201P203parent
10P201P204parent
11P202P203parent
12P202P204parent
13P207P209parent
14P208P209parent
15P207P210parent
16P208P210parent
17P101P102spouse
18P107P108spouse
19P201P202spouse
20P207P208spouse
21P301P302spouse
22P306P307spouse
23P103P104sibling
24P109P110sibling
25P203P204sibling
26P209P210sibling
27P303P304sibling
28P111P203cousin
29P303P103cousin


5. Prints 

title "FAMILY PERSONS — First 12 Rows (Snapshot)";

proc print data=family_enriched(obs=12) label;

  var Household_ID Person_ID Name Gender Relation_To_Head Marital_Status Education 

      Occupation City Birth_Date Age Income Age_Group Income_Band Spouse_ID;

run;

title;

Output:

FAMILY PERSONS — First 12 Rows (Snapshot)

ObsHousehold_IDPerson_IDNameGenderRelation_To_HeadMarital_StatusEducationOccupationCityBirth_DateAgeIncomeAge_GroupIncome_BandSpouse_ID
1HH101P101Ramesh_PatelMaleHeadMarriedUndergraduateAccountantAhmedabad14MAY197847850000AdultMidP102
2HH101P102Meera_PatelFemaleSpouseMarriedPostgraduateTeacherAhmedabad22AUG198044720000AdultMidP101
3HH101P103Arjun_PatelMaleSonSingleUndergraduateEngineer_TraineeAhmedabad10JAN200124420000YouthLower-Mid 
4HH101P104Anya_PatelFemaleDaughterSingleUndergraduateStudentAhmedabad05JUL2003220YouthNil/Low 
5HH101P105Nishit_PatelMaleFatherMarriedDiplomaRetired_ForemanAhmedabad09MAR195075300000SeniorLower-MidP106
6HH101P106Bhavna_PatelFemaleMotherMarriedSchoolHomemakerAhmedabad18DEC1953710SeniorNil/LowP105
7HH101P107Vikas_PatelMaleBrotherMarriedUndergraduateSales_ExecAhmedabad30SEP198341600000AdultMidP108
8HH101P108Kavya_PatelFemaleAuntMarriedUndergraduateBoutique_OwnerAhmedabad12FEB198639550000AdultMidP107
9HH101P109Ria_PatelFemaleNieceSingleSchoolStudentAhmedabad25NOV2011130ChildNil/Low 
10HH101P110Ishaan_PatelMaleNephewSingleSchoolStudentAhmedabad14APR2014110ChildNil/Low 
11HH101P111Aarav_PatelMaleCousinSingleUndergraduateInternAhmedabad02MAY200025300000YouthLower-Mid 
12HH101P112Disha_PatelFemaleCousinSinglePostgraduateMCA_StudentAhmedabad28OCT1999250YouthNil/Low 

6. Distributions 

title "Gender, Marital, Relation & Age Group Distributions";

proc freq data=family_enriched;

  tables Gender Marital_Status Relation_To_Head Age_Group / nocum nopercent;

run;

title;

Output:

Gender, Marital, Relation & Age Group Distributions

The FREQ Procedure

GenderFrequency
Frequency Missing = 2
Female15
Male14
Marital_StatusFrequency
Frequency Missing = 2
Divorced1
Married14
Single12
Widowed2
Relation_To_HeadFrequency
Frequency Missing = 2
Aunt3
Brother2
Cousin3
Daughter3
Father2
Head3
Mother2
Nephew2
Niece2
Son3
Spouse3
Uncle1
Age_GroupFrequency
Adult13
Child8
Senior4
Youth6

7. Sorting and listing 

proc sort data=family_enriched out=by_household;

  by Household_ID Relation_To_Head descending Income;

run;


title "Sorted by Household, Role, and Income (Top 20)";

proc print data=by_household(obs=20);

  by Household_ID;

  id Household_ID;

  var Person_ID Name Relation_To_Head Income;

run;

title;

Output:

Sorted by Household, Role, and Income (Top 20)

Household_IDPerson_IDNameRelation_To_HeadIncome
HH101P108Kavya_PatelAunt550000
P107Vikas_PatelBrother600000
P111Aarav_PatelCousin300000
P112Disha_PatelCousin0
P104Anya_PatelDaughter0
P105Nishit_PatelFather300000
P101Ramesh_PatelHead850000
P106Bhavna_PatelMother0
P110Ishaan_PatelNephew0
P109Ria_PatelNiece0
P103Arjun_PatelSon420000
P102Meera_PatelSpouse720000
Household_IDPerson_IDNameRelation_To_HeadIncome
HH102P208Anita_ReddyAunt700000
P207Nikhil_ReddyBrother950000
P204Maya_ReddyDaughter0
P205Raghav_ReddyFather280000
P201Sridhar_ReddyHead1400000
P206Saraswati_ReddyMother0

8. Aggregations 

title "Household-Level Income Summary";

proc means data=family_enriched n mean median min max sum maxdec=0;

  class Household_ID;

  var Income;

run;

title;

Output:

Household-Level Income Summary

The MEANS Procedure

Analysis Variable : Income
Household_IDN ObsNMeanMedianMinimumMaximumSum
HH101121231166730000008500003740000
HH1021010423000140000014000004230000
HH10377785714700000016000005500000

title "Income by Role Inside Each Household (PROC SUMMARY)";

proc summary data=family_enriched nway;

  class Household_ID Relation_To_Head;

  var Income;

  output out=role_income(drop=_type_ _freq_) sum=Total_Income mean=Avg_Income n=N_People;

run;


proc print data=role_income; run;

title;

Output:

Income by Role Inside Each Household (PROC SUMMARY)

ObsHousehold_IDRelation_To_HeadTotal_IncomeAvg_IncomeN_People
1HH101Aunt5500005500001
2HH101Brother6000006000001
3HH101Cousin3000001500002
4HH101Daughter001
5HH101Father3000003000001
6HH101Head8500008500001
7HH101Mother001
8HH101Nephew001
9HH101Niece001
10HH101Son4200004200001
11HH101Spouse7200007200001
12HH102Aunt7000007000001
13HH102Brother9500009500001
14HH102Daughter001
15HH102Father2800002800001
16HH102Head140000014000001
17HH102Mother001
18HH102Nephew001
19HH102Niece001
20HH102Son001
21HH102Spouse9000009000001
22HH103Aunt001
23HH103Cousin7000007000001
24HH103Daughter8000008000001
25HH103Head160000016000001
26HH103Son6500006500001
27HH103Spouse120000012000001
28HH103Uncle5500005500001

9. SQL: heads view, children counts, spousal combined income 

proc sql;

  create table heads as

  select Household_ID, Person_ID as Head_ID, Name as Head_Name

  from family_enriched

  where Relation_To_Head='Head';


  create view v_family_with_head as

  select f.*, h.Head_ID, h.Head_Name

  from family_enriched f

  left join heads h

    on f.Household_ID = h.Household_ID;


  create table children_counts as

  select r.Person1 as Parent_ID,

         count(*) as N_Children

  from family_rel r

  where r.Rel_Type='parent'

  group by r.Person1;


  create table spousal_income as

  select a.Person_ID as Spouse_A, a.Name as Name_A, a.Income as Income_A,

         b.Person_ID as Spouse_B, b.Name as Name_B, b.Income as Income_B,

         (coalesce(a.Income,0)+coalesce(b.Income,0)) as Combined_Income

  from family_enriched a 

  inner join family_enriched b

    on a.Spouse_ID=b.Person_ID

  where a.Person_ID < b.Person_ID;

quit;


title "Sample of v_family_with_head (View)";

proc print data=v_family_with_head(obs=10); 

  var Household_ID Person_ID Name Relation_To_Head Head_Name Income;

run;

Output:

Sample of v_family_with_head (View)

ObsHousehold_IDPerson_IDNameRelation_To_HeadHead_NameIncome
1     .
2     .
3HH101P112Disha_PatelCousinRamesh_Patel0
4HH101P106Bhavna_PatelMotherRamesh_Patel0
5HH101P101Ramesh_PatelHeadRamesh_Patel850000
6HH101P110Ishaan_PatelNephewRamesh_Patel0
7HH101P105Nishit_PatelFatherRamesh_Patel300000
8HH101P109Ria_PatelNieceRamesh_Patel0
9HH101P103Arjun_PatelSonRamesh_Patel420000
10HH101P104Anya_PatelDaughterRamesh_Patel0

title "Children per Parent";

proc print data=children_counts label;

  label Parent_ID='Parent' N_Children='No. of Children';

run;

Output:

Children per Parent

ObsParentNo. of Children
1P1012
2P1022
3P1072
4P1082
5P2012
6P2022
7P2072
8P2082

title "Spousal Pairs with Combined Income";

proc print data=spousal_income label;

  format Combined_Income comma12.;

run;

title;

Output:

Spousal Pairs with Combined Income

ObsSpouse_AName_AIncome_ASpouse_BName_BIncome_BCombined_Income
1P101Ramesh_Patel850000P102Meera_Patel7200001,570,000
2P105Nishit_Patel300000P106Bhavna_Patel0300,000
3P107Vikas_Patel600000P108Kavya_Patel5500001,150,000
4P201Sridhar_Reddy1400000P202Lakshmi_Reddy9000002,300,000
5P207Nikhil_Reddy950000P208Anita_Reddy7000001,650,000
6P301Vikram_Sharma1600000P302Priya_Sharma12000002,800,000
7P306Harish_Iyer550000P307Latha_Iyer0550,000

10. Proc Transpose pivot 

proc sql;

  create table rel_counts as

  select Household_ID, Relation_To_Head, count(*) as N

  from family_enriched

  group by Household_ID, Relation_To_Head;

quit;


proc sort data=rel_counts; by Household_ID Relation_To_Head; run;


proc transpose data=rel_counts out=rel_pivot prefix=Rel_;

  by Household_ID;

  id Relation_To_Head;

  var N;

run;


title "Pivot: Relation Counts by Household (Wide Layout)";

proc print data=rel_pivot; run;

title;

Output:

Pivot: Relation Counts by Household (Wide Layout)

ObsHousehold_ID_NAME_Rel_AuntRel_BrotherRel_CousinRel_DaughterRel_FatherRel_HeadRel_MotherRel_NephewRel_NieceRel_SonRel_SpouseRel_Uncle
1 N............
2HH101N11211111111.
3HH102N11.11111111.
4HH103N1.11.1...111

11. Tabulate and Report 

title "Income Summary by City and Age Group (PROC TABULATE)";

proc tabulate data=family_enriched format=comma12.;

  class City Age_Group;

  var Income;

  table City,

        Age_Group*(Income*(n mean median sum)) / misstext='0';

run;

title;

Output:

Income Summary by City and Age Group (PROC TABULATE)

 Age_Group
AdultChildSeniorYouth
IncomeIncomeIncomeIncome
NMeanMedianSumNMeanMedianSumNMeanMedianSumNMeanMedianSum
City4680,000660,0002,720,00020002150,000150,000300,0004180,000150,000720,000
Ahmedabad
Hyderabad4987,500925,0003,950,00040002140,000140,000280,0000000
Pune5810,000700,0004,050,000000000002725,000725,0001,450,000

title "Household Roster (PROC REPORT) — Grouped by Household";

proc report data=by_household nowd;

  column Household_ID Person_ID Name Relation_To_Head Education Occupation Income;

  define Household_ID / group;

  define Person_ID / display;

  define Name / display;

  define Relation_To_Head / display;

  define Education / display;

  define Occupation / display;

  define Income / analysis sum format=comma12.;

run;

title;

Output:

Household Roster (PROC REPORT) — Grouped by Household

Household_IDPerson_IDNameRelation_To_HeadEducationOccupationIncome
HH101P108Kavya_PatelAuntUndergraduateBoutique_Owner550,000
 P107Vikas_PatelBrotherUndergraduateSales_Exec600,000
 P111Aarav_PatelCousinUndergraduateIntern300,000
 P112Disha_PatelCousinPostgraduateMCA_Student0
 P104Anya_PatelDaughterUndergraduateStudent0
 P105Nishit_PatelFatherDiplomaRetired_Foreman300,000
 P101Ramesh_PatelHeadUndergraduateAccountant850,000
 P106Bhavna_PatelMotherSchoolHomemaker0
 P110Ishaan_PatelNephewSchoolStudent0
 P109Ria_PatelNieceSchoolStudent0
 P103Arjun_PatelSonUndergraduateEngineer_Trainee420,000
 P102Meera_PatelSpousePostgraduateTeacher720,000
HH102P208Anita_ReddyAuntUndergraduatePharma_Rep700,000
 P207Nikhil_ReddyBrotherUndergraduateBank_Officer950,000
 P204Maya_ReddyDaughterSchoolStudent0
 P205Raghav_ReddyFatherDiplomaRetired_Technician280,000
 P201Sridhar_ReddyHeadPostgraduateSoftware_Manager1,400,000
 P206Saraswati_ReddyMotherSchoolHomemaker0
 P209Rohit_ReddyNephewSchoolStudent0
 P210Sneha_ReddyNieceSchoolStudent0
 P203Teja_ReddySonSchoolStudent0
 P202Lakshmi_ReddySpouseUndergraduateHR_Exec900,000
HH103P307Latha_IyerAuntSchoolHomemaker0
 P305Devika_IyerCousinUndergraduateJournalist700,000
 P304Naina_SharmaDaughterPostgraduateData_Analyst800,000
 P301Vikram_SharmaHeadDoctorateProfessor1,600,000
 P303Kabir_SharmaSonUndergraduateGraphic_Designer650,000
 P302Priya_SharmaSpousePostgraduateArchitect1,200,000
 P306Harish_IyerUncleUndergraduateShop_Owner550,000

12. Simple bar chart 

title "Bar Chart — Count by Relation-To-Head";

proc sgplot data=family_enriched;

  vbar Relation_To_Head / stat=freq;

  xaxis label="Relation to Head";

  yaxis label="Count of Persons";

run;

title;

Output:




13. Macros 

%macro print_household(hh);

  title "Household &hh Member Roster";

  proc print data=family_enriched;

    where Household_ID="&hh";

    var Person_ID Name Gender Relation_To_Head Marital_Status Education Occupation Income;

  run;

  title;

%mend;


%macro role_summary(hh=);

  title "Role-wise Income Summary for &hh";

  proc summary data=family_enriched nway;

    where Household_ID="&hh";

    class Relation_To_Head;

    var Income;

    output out=&hh._role_summary(drop=_:) sum=TotalIncome mean=AvgIncome n=N;

  run;

  proc print data=&hh._role_summary label; 

    label TotalIncome='Total Income' AvgIncome='Average Income' N='Headcount';

    format TotalIncome AvgIncome comma12.;

  run;

  title;

%mend;


%macro kin_report(pid);

  title "Kinship Report for &pid";

  proc sql;

    create table _kin_&pid as

    select "&pid" as Focal length=6,

           r.Rel_Type, r.Person2 as Related_ID, f.Name as Related_Name, 

           f.Relation_To_Head, f.Household_ID, f.City

    from family_rel r

    left join family_enriched f

      on r.Person2 = f.Person_ID

    where r.Person1="&pid";

  quit;

  proc print data=_kin_&pid label;

    label Related_ID='Related Person' Related_Name='Name' Relation_To_Head='Their Role';

  run;

  title;

%mend;


 Macro demos 

%print_household(HH101);

Output:

Household HH101 Member Roster

ObsPerson_IDNameGenderRelation_To_HeadMarital_StatusEducationOccupationIncome
1P101Ramesh_PatelMaleHeadMarriedUndergraduateAccountant850000
2P102Meera_PatelFemaleSpouseMarriedPostgraduateTeacher720000
3P103Arjun_PatelMaleSonSingleUndergraduateEngineer_Trainee420000
4P104Anya_PatelFemaleDaughterSingleUndergraduateStudent0
5P105Nishit_PatelMaleFatherMarriedDiplomaRetired_Foreman300000
6P106Bhavna_PatelFemaleMotherMarriedSchoolHomemaker0
7P107Vikas_PatelMaleBrotherMarriedUndergraduateSales_Exec600000
8P108Kavya_PatelFemaleAuntMarriedUndergraduateBoutique_Owner550000
9P109Ria_PatelFemaleNieceSingleSchoolStudent0
10P110Ishaan_PatelMaleNephewSingleSchoolStudent0
11P111Aarav_PatelMaleCousinSingleUndergraduateIntern300000
12P112Disha_PatelFemaleCousinSinglePostgraduateMCA_Student0

%role_summary(hh=HH102);

Output:

Role-wise Income Summary for HH102

ObsRelation_To_HeadTotal IncomeAverage IncomeHeadcount
1Aunt700,000700,0001
2Brother950,000950,0001
3Daughter001
4Father280,000280,0001
5Head1,400,0001,400,0001
6Mother001
7Nephew001
8Niece001
9Son001
10Spouse900,000900,0001

%kin_report(P103);

Output:

Kinship Report for P103

ObsFocalRel_TypeRelated PersonNameTheir RoleHousehold_IDCity
1P103siblingP104Anya_PatelDaughterHH101Ahmedabad

14. QC checks 

title "QC: Persons Without Recorded Income (should be students/homemakers/retired)";

proc print data=family_enriched;

  where Income=0;

  var Person_ID Name Relation_To_Head Occupation Education;

run;

Output:

QC: Persons Without Recorded Income (should be students/homemakers/retired)

ObsPerson_IDNameRelation_To_HeadOccupationEducation
4P104Anya_PatelDaughterStudentUndergraduate
6P106Bhavna_PatelMotherHomemakerSchool
9P109Ria_PatelNieceStudentSchool
10P110Ishaan_PatelNephewStudentSchool
12P112Disha_PatelCousinMCA_StudentPostgraduate
16P203Teja_ReddySonStudentSchool
17P204Maya_ReddyDaughterStudentSchool
19P206Saraswati_ReddyMotherHomemakerSchool
22P209Rohit_ReddyNephewStudentSchool
23P210Sneha_ReddyNieceStudentSchool
31P307Latha_IyerAuntHomemakerSchool

title "QC: Spouses cross-checked against relationship edges";

proc sql;

  create table spouse_qc as

  select a.Person_ID, a.Name, a.Spouse_ID, b.Person_ID as Spouse2, b.Name as Spouse_Name

  from family_enriched a

  left join family_enriched b

    on a.Spouse_ID=b.Person_ID;

quit;


proc print data=spouse_qc; run;

title;

Output:

QC: Spouses cross-checked against relationship edges

ObsPerson_IDNameSpouse_IDSpouse2Spouse_Name
1     
2P110Ishaan_Patel   
3P112Disha_Patel   
4P103Arjun_Patel   
5P205Raghav_Reddy   
6P210Sneha_Reddy   
7P209Rohit_Reddy   
8P111Aarav_Patel   
9     
10P304Naina_Sharma   
11P305Devika_Iyer   
12P204Maya_Reddy   
13P109Ria_Patel   
14P206Saraswati_Reddy   
15P303Kabir_Sharma   
16P104Anya_Patel   
17P203Teja_Reddy   
18     
19P110Ishaan_Patel   
20P112Disha_Patel   
21P103Arjun_Patel   
22P205Raghav_Reddy   
23P210Sneha_Reddy   
24P209Rohit_Reddy   
25P111Aarav_Patel   
26     
27P304Naina_Sharma   
28P305Devika_Iyer   
29P204Maya_Reddy   
30P109Ria_Patel   
31P206Saraswati_Reddy   
32P303Kabir_Sharma   
33P104Anya_Patel   
34P203Teja_Reddy   
35P102Meera_PatelP101P101Ramesh_Patel
36P101Ramesh_PatelP102P102Meera_Patel
37P106Bhavna_PatelP105P105Nishit_Patel
38P105Nishit_PatelP106P106Bhavna_Patel
39P108Kavya_PatelP107P107Vikas_Patel
40P107Vikas_PatelP108P108Kavya_Patel
41P202Lakshmi_ReddyP201P201Sridhar_Reddy
42P201Sridhar_ReddyP202P202Lakshmi_Reddy
43P208Anita_ReddyP207P207Nikhil_Reddy
44P207Nikhil_ReddyP208P208Anita_Reddy
45P302Priya_SharmaP301P301Vikram_Sharma
46P301Vikram_SharmaP302P302Priya_Sharma
47P307Latha_IyerP306P306Harish_Iyer
48P306Harish_IyerP307P307Latha_Iyer


To Visit My Previous Proc  Means And Nway Option:Click Here
To Visit My Previous Proc Means And CharType Option:Click Here
To Visit My Previous SAS Functions:Click Here
To Visit My Previous Length Statement Using In Many Ways:Click Here








--- FOLLOW OUR BLOG FOR MORE INFORMATION.
--->PLEASE DO COMMENTS AND SHARE OUR BLOG.

PLEASE FOLLOW OUR TELEGRAM CHANNEL CLICK HERE

PLEASE FOLLOW OUR FACEBOOK PAGE  CLICK HERE

PLEASE FOLLOW OUR INSTAGRAM PAGE CLICK HERE






Comments

Post a Comment