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

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:

Obs	Household_ID	Person_ID	Name	Gender	Relation_To_Head	Marital_Status	Education	Occupation	City	Birth_Date	Income	Spouse_ID
1	HH101	P101	Ramesh_Patel	Male	Head	Married	Undergraduate	Accountant	Ahmedabad	14MAY1978	850000	P102
2	HH101	P102	Meera_Patel	Female	Spouse	Married	Postgraduate	Teacher	Ahmedabad	22AUG1980	720000	P101
3	HH101	P103	Arjun_Patel	Male	Son	Single	Undergraduate	Engineer_Trainee	Ahmedabad	10JAN2001	420000
4	HH101	P104	Anya_Patel	Female	Daughter	Single	Undergraduate	Student	Ahmedabad	05JUL2003	0
5	HH101	P105	Nishit_Patel	Male	Father	Married	Diploma	Retired_Foreman	Ahmedabad	09MAR1950	300000	P106
6	HH101	P106	Bhavna_Patel	Female	Mother	Married	School	Homemaker	Ahmedabad	18DEC1953	0	P105
7	HH101	P107	Vikas_Patel	Male	Brother	Married	Undergraduate	Sales_Exec	Ahmedabad	30SEP1983	600000	P108
8	HH101	P108	Kavya_Patel	Female	Aunt	Married	Undergraduate	Boutique_Owner	Ahmedabad	12FEB1986	550000	P107
9	HH101	P109	Ria_Patel	Female	Niece	Single	School	Student	Ahmedabad	25NOV2011	0
10	HH101	P110	Ishaan_Patel	Male	Nephew	Single	School	Student	Ahmedabad	14APR2014	0
11	HH101	P111	Aarav_Patel	Male	Cousin	Single	Undergraduate	Intern	Ahmedabad	02MAY2000	300000
12	HH101	P112	Disha_Patel	Female	Cousin	Single	Postgraduate	MCA_Student	Ahmedabad	28OCT1999	0
13										.	.
14	HH102	P201	Sridhar_Reddy	Male	Head	Married	Postgraduate	Software_Manager	Hyderabad	03JUN1982	1400000	P202
15	HH102	P202	Lakshmi_Reddy	Female	Spouse	Married	Undergraduate	HR_Exec	Hyderabad	17JAN1985	900000	P201
16	HH102	P203	Teja_Reddy	Male	Son	Single	School	Student	Hyderabad	09SEP2012	0
17	HH102	P204	Maya_Reddy	Female	Daughter	Single	School	Student	Hyderabad	22FEB2015	0
18	HH102	P205	Raghav_Reddy	Male	Father	Widowed	Diploma	Retired_Technician	Hyderabad	01APR1956	280000
19	HH102	P206	Saraswati_Reddy	Female	Mother	Widowed	School	Homemaker	Hyderabad	29JUL1958	0
20	HH102	P207	Nikhil_Reddy	Male	Brother	Married	Undergraduate	Bank_Officer	Hyderabad	05MAY1987	950000	P208
21	HH102	P208	Anita_Reddy	Female	Aunt	Married	Undergraduate	Pharma_Rep	Hyderabad	26DEC1989	700000	P207
22	HH102	P209	Rohit_Reddy	Male	Nephew	Single	School	Student	Hyderabad	11AUG2017	0
23	HH102	P210	Sneha_Reddy	Female	Niece	Single	School	Student	Hyderabad	02JAN2020	0
24										.	.
25	HH103	P301	Vikram_Sharma	Male	Head	Married	Doctorate	Professor	Pune	14FEB1975	1600000	P302
26	HH103	P302	Priya_Sharma	Female	Spouse	Married	Postgraduate	Architect	Pune	19MAR1978	1200000	P301
27	HH103	P303	Kabir_Sharma	Male	Son	Single	Undergraduate	Graphic_Designer	Pune	23JUL1999	650000
28	HH103	P304	Naina_Sharma	Female	Daughter	Single	Postgraduate	Data_Analyst	Pune	12DEC2001	800000
29	HH103	P305	Devika_Iyer	Female	Cousin	Divorced	Undergraduate	Journalist	Pune	30OCT1984	700000
30	HH103	P306	Harish_Iyer	Male	Uncle	Married	Undergraduate	Shop_Owner	Pune	07JAN1968	550000	P307
31	HH103	P307	Latha_Iyer	Female	Aunt	Married	School	Homemaker	Pune	22MAY1971	0	P306

3. Structure check 

proc contents data=family_persons varnum; run;

Output:

The CONTENTS Procedure

Data Set Name	WORK.FAMILY_PERSONS	Observations	31
Member Type	DATA	Variables	12
Engine	V9	Indexes	0
Created	08/16/2025 19:43:23	Observation Length	136
Last Modified	08/16/2025 19:43:23	Deleted Observations	0
Protection		Compressed	NO
Data Set Type		Sorted	NO
Label
Data Representation	SOLARIS_X86_64, LINUX_X86_64, ALPHA_TRU64, LINUX_IA64
Encoding	utf-8 Unicode (UTF-8)

Engine/Host Dependent Information
Data Set Page Size	131072
Number of Data Set Pages	1
First Data Page	1
Max Obs per Page	962
Obs in First Data Page	31
Number of Data Set Repairs	0
Filename	/saswork/SAS_work1AA000005216_odaws02-apse1-2.oda.sas.com/SAS_work38DE00005216_odaws02-apse1-2.oda.sas.com/family_persons.sas7bdat
Release Created	9.0401M8
Host Created	Linux
Inode Number	201328344
Access Permission	rw-r--r--
Owner Name	u63247146
File Size	256KB
File Size (bytes)	262144

Variables in Creation Order
#	Variable	Type	Len	Format
1	Household_ID	Char	6
2	Person_ID	Char	6
3	Name	Char	20
4	Gender	Char	1	$GENDER.
5	Relation_To_Head	Char	12	$RELHD.
6	Marital_Status	Char	8	$MARSTAT.
7	Education	Char	12	$EDU.
8	Occupation	Char	20
9	City	Char	20
10	Birth_Date	Num	8	DATE9.
11	Income	Num	8
12	Spouse_ID	Char	8

 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:

Obs	Household_ID	Person_ID	Name	Gender	Relation_To_Head	Marital_Status	Education	Occupation	City	Birth_Date	Income	Spouse_ID	Age	Age_Group	Income_Band
1	HH101	P101	Ramesh_Patel	Male	Head	Married	Undergraduate	Accountant	Ahmedabad	14MAY1978	850000	P102	47	Adult	Mid
2	HH101	P102	Meera_Patel	Female	Spouse	Married	Postgraduate	Teacher	Ahmedabad	22AUG1980	720000	P101	44	Adult	Mid
3	HH101	P103	Arjun_Patel	Male	Son	Single	Undergraduate	Engineer_Trainee	Ahmedabad	10JAN2001	420000		24	Youth	Lower-Mid
4	HH101	P104	Anya_Patel	Female	Daughter	Single	Undergraduate	Student	Ahmedabad	05JUL2003	0		22	Youth	Nil/Low
5	HH101	P105	Nishit_Patel	Male	Father	Married	Diploma	Retired_Foreman	Ahmedabad	09MAR1950	300000	P106	75	Senior	Lower-Mid
6	HH101	P106	Bhavna_Patel	Female	Mother	Married	School	Homemaker	Ahmedabad	18DEC1953	0	P105	71	Senior	Nil/Low
7	HH101	P107	Vikas_Patel	Male	Brother	Married	Undergraduate	Sales_Exec	Ahmedabad	30SEP1983	600000	P108	41	Adult	Mid
8	HH101	P108	Kavya_Patel	Female	Aunt	Married	Undergraduate	Boutique_Owner	Ahmedabad	12FEB1986	550000	P107	39	Adult	Mid
9	HH101	P109	Ria_Patel	Female	Niece	Single	School	Student	Ahmedabad	25NOV2011	0		13	Child	Nil/Low
10	HH101	P110	Ishaan_Patel	Male	Nephew	Single	School	Student	Ahmedabad	14APR2014	0		11	Child	Nil/Low
11	HH101	P111	Aarav_Patel	Male	Cousin	Single	Undergraduate	Intern	Ahmedabad	02MAY2000	300000		25	Youth	Lower-Mid
12	HH101	P112	Disha_Patel	Female	Cousin	Single	Postgraduate	MCA_Student	Ahmedabad	28OCT1999	0		25	Youth	Nil/Low
13										.	.		.	Child	Nil/Low
14	HH102	P201	Sridhar_Reddy	Male	Head	Married	Postgraduate	Software_Manager	Hyderabad	03JUN1982	1400000	P202	43	Adult	High
15	HH102	P202	Lakshmi_Reddy	Female	Spouse	Married	Undergraduate	HR_Exec	Hyderabad	17JAN1985	900000	P201	40	Adult	Upper-Mid
16	HH102	P203	Teja_Reddy	Male	Son	Single	School	Student	Hyderabad	09SEP2012	0		12	Child	Nil/Low
17	HH102	P204	Maya_Reddy	Female	Daughter	Single	School	Student	Hyderabad	22FEB2015	0		10	Child	Nil/Low
18	HH102	P205	Raghav_Reddy	Male	Father	Widowed	Diploma	Retired_Technician	Hyderabad	01APR1956	280000		69	Senior	Lower-Mid
19	HH102	P206	Saraswati_Reddy	Female	Mother	Widowed	School	Homemaker	Hyderabad	29JUL1958	0		67	Senior	Nil/Low
20	HH102	P207	Nikhil_Reddy	Male	Brother	Married	Undergraduate	Bank_Officer	Hyderabad	05MAY1987	950000	P208	38	Adult	Upper-Mid
21	HH102	P208	Anita_Reddy	Female	Aunt	Married	Undergraduate	Pharma_Rep	Hyderabad	26DEC1989	700000	P207	35	Adult	Mid
22	HH102	P209	Rohit_Reddy	Male	Nephew	Single	School	Student	Hyderabad	11AUG2017	0		8	Child	Nil/Low
23	HH102	P210	Sneha_Reddy	Female	Niece	Single	School	Student	Hyderabad	02JAN2020	0		5	Child	Nil/Low
24										.	.		.	Child	Nil/Low
25	HH103	P301	Vikram_Sharma	Male	Head	Married	Doctorate	Professor	Pune	14FEB1975	1600000	P302	50	Adult	High
26	HH103	P302	Priya_Sharma	Female	Spouse	Married	Postgraduate	Architect	Pune	19MAR1978	1200000	P301	47	Adult	Upper-Mid
27	HH103	P303	Kabir_Sharma	Male	Son	Single	Undergraduate	Graphic_Designer	Pune	23JUL1999	650000		26	Youth	Mid
28	HH103	P304	Naina_Sharma	Female	Daughter	Single	Postgraduate	Data_Analyst	Pune	12DEC2001	800000		23	Youth	Mid
29	HH103	P305	Devika_Iyer	Female	Cousin	Divorced	Undergraduate	Journalist	Pune	30OCT1984	700000		40	Adult	Mid
30	HH103	P306	Harish_Iyer	Male	Uncle	Married	Undergraduate	Shop_Owner	Pune	07JAN1968	550000	P307	57	Adult	Mid
31	HH103	P307	Latha_Iyer	Female	Aunt	Married	School	Homemaker	Pune	22MAY1971	0	P306	54	Adult	Nil/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:

Obs	Person1	Person2	Rel_Type
1	P101	P103	parent
2	P101	P104	parent
3	P102	P103	parent
4	P102	P104	parent
5	P107	P109	parent
6	P108	P109	parent
7	P107	P110	parent
8	P108	P110	parent
9	P201	P203	parent
10	P201	P204	parent
11	P202	P203	parent
12	P202	P204	parent
13	P207	P209	parent
14	P208	P209	parent
15	P207	P210	parent
16	P208	P210	parent
17	P101	P102	spouse
18	P107	P108	spouse
19	P201	P202	spouse
20	P207	P208	spouse
21	P301	P302	spouse
22	P306	P307	spouse
23	P103	P104	sibling
24	P109	P110	sibling
25	P203	P204	sibling
26	P209	P210	sibling
27	P303	P304	sibling
28	P111	P203	cousin
29	P303	P103	cousin

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)

Obs	Household_ID	Person_ID	Name	Gender	Relation_To_Head	Marital_Status	Education	Occupation	City	Birth_Date	Age	Income	Age_Group	Income_Band	Spouse_ID
1	HH101	P101	Ramesh_Patel	Male	Head	Married	Undergraduate	Accountant	Ahmedabad	14MAY1978	47	850000	Adult	Mid	P102
2	HH101	P102	Meera_Patel	Female	Spouse	Married	Postgraduate	Teacher	Ahmedabad	22AUG1980	44	720000	Adult	Mid	P101
3	HH101	P103	Arjun_Patel	Male	Son	Single	Undergraduate	Engineer_Trainee	Ahmedabad	10JAN2001	24	420000	Youth	Lower-Mid
4	HH101	P104	Anya_Patel	Female	Daughter	Single	Undergraduate	Student	Ahmedabad	05JUL2003	22	0	Youth	Nil/Low
5	HH101	P105	Nishit_Patel	Male	Father	Married	Diploma	Retired_Foreman	Ahmedabad	09MAR1950	75	300000	Senior	Lower-Mid	P106
6	HH101	P106	Bhavna_Patel	Female	Mother	Married	School	Homemaker	Ahmedabad	18DEC1953	71	0	Senior	Nil/Low	P105
7	HH101	P107	Vikas_Patel	Male	Brother	Married	Undergraduate	Sales_Exec	Ahmedabad	30SEP1983	41	600000	Adult	Mid	P108
8	HH101	P108	Kavya_Patel	Female	Aunt	Married	Undergraduate	Boutique_Owner	Ahmedabad	12FEB1986	39	550000	Adult	Mid	P107
9	HH101	P109	Ria_Patel	Female	Niece	Single	School	Student	Ahmedabad	25NOV2011	13	0	Child	Nil/Low
10	HH101	P110	Ishaan_Patel	Male	Nephew	Single	School	Student	Ahmedabad	14APR2014	11	0	Child	Nil/Low
11	HH101	P111	Aarav_Patel	Male	Cousin	Single	Undergraduate	Intern	Ahmedabad	02MAY2000	25	300000	Youth	Lower-Mid
12	HH101	P112	Disha_Patel	Female	Cousin	Single	Postgraduate	MCA_Student	Ahmedabad	28OCT1999	25	0	Youth	Nil/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

Gender	Frequency
Frequency Missing = 2
Female	15
Male	14

Marital_Status	Frequency
Frequency Missing = 2
Divorced	1
Married	14
Single	12
Widowed	2

Relation_To_Head	Frequency
Frequency Missing = 2
Aunt	3
Brother	2
Cousin	3
Daughter	3
Father	2
Head	3
Mother	2
Nephew	2
Niece	2
Son	3
Spouse	3
Uncle	1

Age_Group	Frequency
Adult	13
Child	8
Senior	4
Youth	6

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_ID	Person_ID	Name	Relation_To_Head	Income
HH101	P108	Kavya_Patel	Aunt	550000
	P107	Vikas_Patel	Brother	600000
	P111	Aarav_Patel	Cousin	300000
	P112	Disha_Patel	Cousin	0
	P104	Anya_Patel	Daughter	0
	P105	Nishit_Patel	Father	300000
	P101	Ramesh_Patel	Head	850000
	P106	Bhavna_Patel	Mother	0
	P110	Ishaan_Patel	Nephew	0
	P109	Ria_Patel	Niece	0
	P103	Arjun_Patel	Son	420000
	P102	Meera_Patel	Spouse	720000

Household_ID	Person_ID	Name	Relation_To_Head	Income
HH102	P208	Anita_Reddy	Aunt	700000
	P207	Nikhil_Reddy	Brother	950000
	P204	Maya_Reddy	Daughter	0
	P205	Raghav_Reddy	Father	280000
	P201	Sridhar_Reddy	Head	1400000
	P206	Saraswati_Reddy	Mother	0

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_ID	N Obs	N	Mean	Median	Minimum	Maximum	Sum
HH101	12	12	311667	300000	0	850000	3740000
HH102	10	10	423000	140000	0	1400000	4230000
HH103	7	7	785714	700000	0	1600000	5500000

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)

Obs	Household_ID	Relation_To_Head	Total_Income	Avg_Income	N_People
1	HH101	Aunt	550000	550000	1
2	HH101	Brother	600000	600000	1
3	HH101	Cousin	300000	150000	2
4	HH101	Daughter	0	0	1
5	HH101	Father	300000	300000	1
6	HH101	Head	850000	850000	1
7	HH101	Mother	0	0	1
8	HH101	Nephew	0	0	1
9	HH101	Niece	0	0	1
10	HH101	Son	420000	420000	1
11	HH101	Spouse	720000	720000	1
12	HH102	Aunt	700000	700000	1
13	HH102	Brother	950000	950000	1
14	HH102	Daughter	0	0	1
15	HH102	Father	280000	280000	1
16	HH102	Head	1400000	1400000	1
17	HH102	Mother	0	0	1
18	HH102	Nephew	0	0	1
19	HH102	Niece	0	0	1
20	HH102	Son	0	0	1
21	HH102	Spouse	900000	900000	1
22	HH103	Aunt	0	0	1
23	HH103	Cousin	700000	700000	1
24	HH103	Daughter	800000	800000	1
25	HH103	Head	1600000	1600000	1
26	HH103	Son	650000	650000	1
27	HH103	Spouse	1200000	1200000	1
28	HH103	Uncle	550000	550000	1

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)

Obs	Household_ID	Person_ID	Name	Relation_To_Head	Head_Name	Income
1						.
2						.
3	HH101	P112	Disha_Patel	Cousin	Ramesh_Patel	0
4	HH101	P106	Bhavna_Patel	Mother	Ramesh_Patel	0
5	HH101	P101	Ramesh_Patel	Head	Ramesh_Patel	850000
6	HH101	P110	Ishaan_Patel	Nephew	Ramesh_Patel	0
7	HH101	P105	Nishit_Patel	Father	Ramesh_Patel	300000
8	HH101	P109	Ria_Patel	Niece	Ramesh_Patel	0
9	HH101	P103	Arjun_Patel	Son	Ramesh_Patel	420000
10	HH101	P104	Anya_Patel	Daughter	Ramesh_Patel	0

title "Children per Parent";

proc print data=children_counts label;

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

run;

Output:

Children per Parent

Obs	Parent	No. of Children
1	P101	2
2	P102	2
3	P107	2
4	P108	2
5	P201	2
6	P202	2
7	P207	2
8	P208	2

title "Spousal Pairs with Combined Income";

proc print data=spousal_income label;

  format Combined_Income comma12.;

run;

title;

Output:

Spousal Pairs with Combined Income

Obs	Spouse_A	Name_A	Income_A	Spouse_B	Name_B	Income_B	Combined_Income
1	P101	Ramesh_Patel	850000	P102	Meera_Patel	720000	1,570,000
2	P105	Nishit_Patel	300000	P106	Bhavna_Patel	0	300,000
3	P107	Vikas_Patel	600000	P108	Kavya_Patel	550000	1,150,000
4	P201	Sridhar_Reddy	1400000	P202	Lakshmi_Reddy	900000	2,300,000
5	P207	Nikhil_Reddy	950000	P208	Anita_Reddy	700000	1,650,000
6	P301	Vikram_Sharma	1600000	P302	Priya_Sharma	1200000	2,800,000
7	P306	Harish_Iyer	550000	P307	Latha_Iyer	0	550,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)

Obs	Household_ID	_NAME_	Rel_Aunt	Rel_Brother	Rel_Cousin	Rel_Daughter	Rel_Father	Rel_Head	Rel_Mother	Rel_Nephew	Rel_Niece	Rel_Son	Rel_Spouse	Rel_Uncle
1		N	.	.	.	.	.	.	.	.	.	.	.	.
2	HH101	N	1	1	2	1	1	1	1	1	1	1	1	.
3	HH102	N	1	1	.	1	1	1	1	1	1	1	1	.
4	HH103	N	1	.	1	1	.	1	.	.	.	1	1	1

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
	Adult				Child				Senior				Youth
	Income				Income				Income				Income
	N	Mean	Median	Sum	N	Mean	Median	Sum	N	Mean	Median	Sum	N	Mean	Median	Sum
City	4	680,000	660,000	2,720,000	2	0	0	0	2	150,000	150,000	300,000	4	180,000	150,000	720,000
Ahmedabad
Hyderabad	4	987,500	925,000	3,950,000	4	0	0	0	2	140,000	140,000	280,000	0	0	0	0
Pune	5	810,000	700,000	4,050,000	0	0	0	0	0	0	0	0	2	725,000	725,000	1,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_ID	Person_ID	Name	Relation_To_Head	Education	Occupation	Income
HH101	P108	Kavya_Patel	Aunt	Undergraduate	Boutique_Owner	550,000
	P107	Vikas_Patel	Brother	Undergraduate	Sales_Exec	600,000
	P111	Aarav_Patel	Cousin	Undergraduate	Intern	300,000
	P112	Disha_Patel	Cousin	Postgraduate	MCA_Student	0
	P104	Anya_Patel	Daughter	Undergraduate	Student	0
	P105	Nishit_Patel	Father	Diploma	Retired_Foreman	300,000
	P101	Ramesh_Patel	Head	Undergraduate	Accountant	850,000
	P106	Bhavna_Patel	Mother	School	Homemaker	0
	P110	Ishaan_Patel	Nephew	School	Student	0
	P109	Ria_Patel	Niece	School	Student	0
	P103	Arjun_Patel	Son	Undergraduate	Engineer_Trainee	420,000
	P102	Meera_Patel	Spouse	Postgraduate	Teacher	720,000
HH102	P208	Anita_Reddy	Aunt	Undergraduate	Pharma_Rep	700,000
	P207	Nikhil_Reddy	Brother	Undergraduate	Bank_Officer	950,000
	P204	Maya_Reddy	Daughter	School	Student	0
	P205	Raghav_Reddy	Father	Diploma	Retired_Technician	280,000
	P201	Sridhar_Reddy	Head	Postgraduate	Software_Manager	1,400,000
	P206	Saraswati_Reddy	Mother	School	Homemaker	0
	P209	Rohit_Reddy	Nephew	School	Student	0
	P210	Sneha_Reddy	Niece	School	Student	0
	P203	Teja_Reddy	Son	School	Student	0
	P202	Lakshmi_Reddy	Spouse	Undergraduate	HR_Exec	900,000
HH103	P307	Latha_Iyer	Aunt	School	Homemaker	0
	P305	Devika_Iyer	Cousin	Undergraduate	Journalist	700,000
	P304	Naina_Sharma	Daughter	Postgraduate	Data_Analyst	800,000
	P301	Vikram_Sharma	Head	Doctorate	Professor	1,600,000
	P303	Kabir_Sharma	Son	Undergraduate	Graphic_Designer	650,000
	P302	Priya_Sharma	Spouse	Postgraduate	Architect	1,200,000
	P306	Harish_Iyer	Uncle	Undergraduate	Shop_Owner	550,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

Obs	Person_ID	Name	Gender	Relation_To_Head	Marital_Status	Education	Occupation	Income
1	P101	Ramesh_Patel	Male	Head	Married	Undergraduate	Accountant	850000
2	P102	Meera_Patel	Female	Spouse	Married	Postgraduate	Teacher	720000
3	P103	Arjun_Patel	Male	Son	Single	Undergraduate	Engineer_Trainee	420000
4	P104	Anya_Patel	Female	Daughter	Single	Undergraduate	Student	0
5	P105	Nishit_Patel	Male	Father	Married	Diploma	Retired_Foreman	300000
6	P106	Bhavna_Patel	Female	Mother	Married	School	Homemaker	0
7	P107	Vikas_Patel	Male	Brother	Married	Undergraduate	Sales_Exec	600000
8	P108	Kavya_Patel	Female	Aunt	Married	Undergraduate	Boutique_Owner	550000
9	P109	Ria_Patel	Female	Niece	Single	School	Student	0
10	P110	Ishaan_Patel	Male	Nephew	Single	School	Student	0
11	P111	Aarav_Patel	Male	Cousin	Single	Undergraduate	Intern	300000
12	P112	Disha_Patel	Female	Cousin	Single	Postgraduate	MCA_Student	0

%role_summary(hh=HH102);

Output:

Role-wise Income Summary for HH102

Obs	Relation_To_Head	Total Income	Average Income	Headcount
1	Aunt	700,000	700,000	1
2	Brother	950,000	950,000	1
3	Daughter	0	0	1
4	Father	280,000	280,000	1
5	Head	1,400,000	1,400,000	1
6	Mother	0	0	1
7	Nephew	0	0	1
8	Niece	0	0	1
9	Son	0	0	1
10	Spouse	900,000	900,000	1

%kin_report(P103);

Output:

Kinship Report for P103

Obs	Focal	Rel_Type	Related Person	Name	Their Role	Household_ID	City
1	P103	sibling	P104	Anya_Patel	Daughter	HH101	Ahmedabad

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)

Obs	Person_ID	Name	Relation_To_Head	Occupation	Education
4	P104	Anya_Patel	Daughter	Student	Undergraduate
6	P106	Bhavna_Patel	Mother	Homemaker	School
9	P109	Ria_Patel	Niece	Student	School
10	P110	Ishaan_Patel	Nephew	Student	School
12	P112	Disha_Patel	Cousin	MCA_Student	Postgraduate
16	P203	Teja_Reddy	Son	Student	School
17	P204	Maya_Reddy	Daughter	Student	School
19	P206	Saraswati_Reddy	Mother	Homemaker	School
22	P209	Rohit_Reddy	Nephew	Student	School
23	P210	Sneha_Reddy	Niece	Student	School
31	P307	Latha_Iyer	Aunt	Homemaker	School

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

Obs	Person_ID	Name	Spouse_ID	Spouse2	Spouse_Name
1
2	P110	Ishaan_Patel
3	P112	Disha_Patel
4	P103	Arjun_Patel
5	P205	Raghav_Reddy
6	P210	Sneha_Reddy
7	P209	Rohit_Reddy
8	P111	Aarav_Patel
9
10	P304	Naina_Sharma
11	P305	Devika_Iyer
12	P204	Maya_Reddy
13	P109	Ria_Patel
14	P206	Saraswati_Reddy
15	P303	Kabir_Sharma
16	P104	Anya_Patel
17	P203	Teja_Reddy
18
19	P110	Ishaan_Patel
20	P112	Disha_Patel
21	P103	Arjun_Patel
22	P205	Raghav_Reddy
23	P210	Sneha_Reddy
24	P209	Rohit_Reddy
25	P111	Aarav_Patel
26
27	P304	Naina_Sharma
28	P305	Devika_Iyer
29	P204	Maya_Reddy
30	P109	Ria_Patel
31	P206	Saraswati_Reddy
32	P303	Kabir_Sharma
33	P104	Anya_Patel
34	P203	Teja_Reddy
35	P102	Meera_Patel	P101	P101	Ramesh_Patel
36	P101	Ramesh_Patel	P102	P102	Meera_Patel
37	P106	Bhavna_Patel	P105	P105	Nishit_Patel
38	P105	Nishit_Patel	P106	P106	Bhavna_Patel
39	P108	Kavya_Patel	P107	P107	Vikas_Patel
40	P107	Vikas_Patel	P108	P108	Kavya_Patel
41	P202	Lakshmi_Reddy	P201	P201	Sridhar_Reddy
42	P201	Sridhar_Reddy	P202	P202	Lakshmi_Reddy
43	P208	Anita_Reddy	P207	P207	Nikhil_Reddy
44	P207	Nikhil_Reddy	P208	P208	Anita_Reddy
45	P302	Priya_Sharma	P301	P301	Vikram_Sharma
46	P301	Vikram_Sharma	P302	P302	Priya_Sharma
47	P307	Latha_Iyer	P306	P306	Harish_Iyer
48	P306	Harish_Iyer	P307	P307	Latha_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