prepping census data aggregated by multiple groups

[Cruise]

Hi folks,

I used following approach many times in prep of my census based population data to aggregate the population counts by age, gender and race/ethnicity categories. I'm curious if you had any shortcut or alternative approach to this long and repetitive programming approach? Also, please let me know if you agree with my approach following.

PROC IMPORT DATAFILE="...\Censusdata\COUNTY\DEC_10_SF1_P12I_AGE_SEX_NHW_COUNTY\NHW.CSV"
OUT=CEN_NHW DBMS=CSV REPLACE; DATAROW=3;
RUN;

%SYMDEL; 
%MACRO MODELS(CENSUS,CENSUS_COUNTS);
DATA &CENSUS; SET &CENSUS;
COUNTY=SUBSTRN(GEO_id2,3,3);
RUN;
PROC SQL;
CREATE TABLE &CENSUS_COUNTS AS SELECT COUNTY,
	sum(D003,D004,D005,D006,D007,D008,D009,D010) as F0_25,
	sum(D011,D012) as F25_35,
	sum(D013,D014) as F35_45,
	sum(D015,D016) as F45_55,
	sum(D017,D018,D019) as F55_65,
	sum(D020,D021,D022) as F65_75,
	sum(D023,D024,D025) as F75,
	sum(D027, D028, D029, D030, D031, D032, D033, D034) as M0_25,
	sum(D035,D036) as M25_35,
	sum(D037,D038) as M35_45,
	sum(D039,D040) as M45_55,
	sum(D041,D042,D043) as M55_65,
	sum(D044,D045,D046) as M65_75,
	sum(D047,D048,D049) as M75 FROM &CENSUS
ORDER by COUNTY;
QUIT; 
%MEND MODELS;
%MODELS(CEN_ASIAN,CEN_SUM_ASIAN);
%MODELS(CEN_NHB,CEN_SUM_NHB);
%MODELS(CEN_OTHER,CEN_SUM_OTHER);
%MODELS(CEN_HISP,CEN_SUM_HISP);
%MODELS(CEN_NHW,CEN_SUM_NHW);

data pop1; set CEN_SUM_NHW(rename=(M0_25=pop));
sex = 1; raceth = 1; agecat = 1;
KEEP COUNTY SEX RACETH AGECAT POP; 
data pop2; set CEN_SUM_NHW(rename=(M25_35=pop));
sex = 1; raceth = 1; agecat = 2;
KEEP COUNTY SEX RACETH AGECAT POP; 
data pop3; set CEN_SUM_NHW(rename=(M35_45=pop));
sex = 1; raceth = 1; agecat = 3;
KEEP COUNTY SEX RACETH AGECAT POP; 
data pop4; set CEN_SUM_NHW(rename=(M45_55=pop));
sex = 1; raceth = 1; agecat = 4;
KEEP COUNTY SEX RACETH AGECAT POP; 
data pop5; set CEN_SUM_NHW(rename=(M55_65=pop));
sex = 1; raceth = 1; agecat = 5;
KEEP COUNTY SEX RACETH AGECAT POP; 
data pop6; set CEN_SUM_NHW(rename=(M65_75=pop));
sex = 1; raceth = 1; agecat = 6;
KEEP COUNTY SEX RACETH AGECAT POP; 
data pop7; set CEN_SUM_NHW(rename=(M75=pop));
sex = 1; raceth = 1; agecat = 7; 
KEEP COUNTY SEX RACETH AGECAT POP; 
data pop8; set CEN_SUM_NHW(rename=(F0_25=pop));
sex = 2; raceth = 1; agecat = 1;
KEEP COUNTY SEX RACETH AGECAT POP; 
data pop9; set CEN_SUM_NHW(rename=(F25_35=pop));
sex = 2; raceth = 1; agecat = 2;
KEEP COUNTY SEX RACETH AGECAT POP; 
data pop10; set CEN_SUM_NHW(rename=(F35_45=pop));
sex = 2; raceth = 1; agecat = 3;
KEEP COUNTY SEX RACETH AGECAT POP; 
data pop11; set CEN_SUM_NHW(rename=(F45_55=pop));
sex = 2; raceth = 1; agecat = 4;
KEEP COUNTY SEX RACETH AGECAT POP; 
data pop12; set CEN_SUM_NHW(rename=(F55_65=pop));
sex = 2; raceth = 1; agecat = 5;
KEEP COUNTY SEX RACETH AGECAT POP; 
data pop13; set CEN_SUM_NHW(rename=(F65_75=pop));
sex = 2; raceth = 1; agecat = 6;
KEEP COUNTY SEX RACETH AGECAT POP; 
data pop14; set CEN_SUM_NHW(rename=(F75=pop));
sex = 2; raceth = 1; agecat = 7; 
KEEP COUNTY SEX RACETH AGECAT POP;
RUN;  
data pop15; set CEN_SUM_ASIAN(rename=(M0_25=pop));
sex = 1; raceth = 1; agecat = 1;
KEEP COUNTY SEX RACETH AGECAT POP; 
data pop16; set CEN_SUM_ASIAN(rename=(M25_35=pop));
sex = 1; raceth = 1; agecat = 2;
KEEP COUNTY SEX RACETH AGECAT POP; 
data pop17; set CEN_SUM_ASIAN(rename=(M35_45=pop));
sex = 1; raceth = 1; agecat = 3;
KEEP COUNTY SEX RACETH AGECAT POP; 
data pop18; set CEN_SUM_ASIAN(rename=(M45_55=pop));
sex = 1; raceth = 1; agecat = 4;
KEEP COUNTY SEX RACETH AGECAT POP; 
data pop19; set CEN_SUM_ASIAN(rename=(M55_65=pop));
sex = 1; raceth = 1; agecat = 5;
KEEP COUNTY SEX RACETH AGECAT POP; 
data pop20; set CEN_SUM_ASIAN(rename=(M65_75=pop));
sex = 1; raceth = 1; agecat = 6;
KEEP COUNTY SEX RACETH AGECAT POP; 
data pop21; set CEN_SUM_ASIAN(rename=(M75=pop));
sex = 1; raceth = 1; agecat = 7; 
KEEP COUNTY SEX RACETH AGECAT POP; 
data pop22; set CEN_SUM_ASIAN(rename=(F0_25=pop));
sex = 2; raceth = 1; agecat = 1;
KEEP COUNTY SEX RACETH AGECAT POP; 
data pop23; set CEN_SUM_ASIAN(rename=(F25_35=pop));
sex = 2; raceth = 1; agecat = 2;
KEEP COUNTY SEX RACETH AGECAT POP; 
data pop24; set CEN_SUM_ASIAN(rename=(F35_45=pop));
sex = 2; raceth = 1; agecat = 3;
KEEP COUNTY SEX RACETH AGECAT POP; 
data pop25; set CEN_SUM_ASIAN(rename=(F45_55=pop));
sex = 2; raceth = 1; agecat = 4;
KEEP COUNTY SEX RACETH AGECAT POP; 
data pop26; set CEN_SUM_ASIAN(rename=(F55_65=pop));
sex = 2; raceth = 1; agecat = 5;
KEEP COUNTY SEX RACETH AGECAT POP; 
data pop27; set CEN_SUM_ASIAN(rename=(F65_75=pop));
sex = 2; raceth = 1; agecat = 6;
KEEP COUNTY SEX RACETH AGECAT POP; 
data pop28; set CEN_SUM_ASIAN(rename=(F75=pop));
sex = 2; raceth = 1; agecat = 7; 
KEEP COUNTY SEX RACETH AGECAT POP;

....
THE SAME GOES FOR THE REST OF THE RACE/ETHNICITY CATEGORIES...
....
DATA ALL_POP; SET POP:; RUN; 

[gamotte · Accepted solution]

Hello,

You can try something like that

%macro prep(dsname, source, race, sex, categ, pop);

    data &dsname;
        set CEN_SUM_&source.(rename=(&pop.=pop));

        sex=&sex.;
        raceth=&race.;
        agecat=&categ.;
    run;

%mend;

data _NULL_;

    sexnum=0;
    i=0;

    do sex="M", "F";
        sexnum+1;
        racenum=0;
        do race="NWH", "ASIAN";
            racenum+1;
            categnum=0;
            do categ="0_25", "25_35", "35_45", "45_55", "55_65", "65_75", "75";
                i+1;
                categnum+1;
                call execute(cats('%prep(pop',i,',',race,',',racenum,',',sexnum,',',categnum,',',sex,categ,');'));
            end;
        end;
    end;
run;

[Cruise]

Thank you. It worked!