Hello experts,   I try to use proc sql to create a new variable from the original variable p1. the new variable (p2) is aggregated variable by people's id and their area. my original data like in table 1 and the result that i want is like in table 2: table 1 id year area s1 o1 P1 1 2020     0 1 1 2020 NEW YORK   0 0 1 2020 NEW YORK 1 1 0   table 2 ID area year O2 S2 P2 1   2020 0 0 1 1 NY 2020 1 1 0   the sas code i used is: proc sql; create table data.table2 as select distinct id, area, year, s1,  case when sum(p1)>=1 then 1 else 0 end as p2, case when sum(o1)>=1 then 1 else 0 end as o2, case when sum(s1)>=1 then 1 else 0 end as s2 from data.table1 group by id , area; quit;  the P2 (in red color) suppose to be 0 but my result is 1. i don't know what's wrong with my code. please help. ID area   O2 S2 P2 1   2020 0 0 1 1 NY 2020 1 1 1 ... View more

hello,   i am new to the proc sql in sas. my dataset like this: Obs id area year 5818 1 NEW YORK 2020 5819 1 NEW YORK 2021 5820 1 NEWARK 2020 5821 1 NEWARK 2021 i try to group them by id, year and area and i used the following code: i think i supposed to get only two records but instead, i still got the 4 records. i can't figure out what's wrong with this code.  proc sql; create table data.clientematga2 as select distinct a.id, a.area, a.year from data.clientematga a group by euci42, ematga , a.year; quit;   ... View more

Hello, i have about 30 datasets that i need to write a macro to get the proc contents from each dataset. i am new to the macro. i write the following but it doesn't work. can any expert take a look at my code:   %macro C1 (ennloop, list); %do i = 1 %to &endloop.; %let dem = %scan(&list., &i.); proc contents data=&list; run; end; %mend; %C1(2, c2010 c2016 d2016 d2017 f2016 f2019); ... View more

hello all sas experts:   i have a folder that stored a lot of datasets from different years. the dataset are: Cinfo2004 to Cinfo2021, Ginfo2006 to Ginfo2021, Ginfo_Geocode2010 to Ginfo_Geocode2021.  I would like to create a sas code to list all data files in the directory by providing the earliest and latest years for each type of the dataset. for example Cinfo’s earliest year is 2004 and the latest is 2021 and Ginfo's earliest year is 2006 and the latest is 2021. i know there is a way to write a sas code to pull out these information but don't know how to do it.   ... View more

Dear Expert: i want to compare the format, label and variable names in two dataset. and i used the following code to do it. the code work very well for two of my compared datasets but the same code didn't work for another two compared dataset. there is no error message but log says  : "No observations were selected from data set SASHELP.VCOLUMN." . i just can't figure out the reason that caused this issue. please advise. data work.g2020; set rsrbase0.g2020; run; data work.g2021; set rsrbase1.g2021; run; proc tabulate data=sashelp.vcolumn; title "compare name and types"; where libname='WORK' and memname in ('g2020' 'g2021'); class memname name type ; table name*type, memname ; run;   I used the same code for another two dataset and works well: data work.c2020; set rsrbase0.c2020; run; data work.c2021; set rsrbase1.c2021; run; proc tabulate data=sashelp.vcolumn; title "compare name and types"; where libname='WORK' and memname in ('c2020' 'c2021'); class memname name type ; table name*type, memname ; run; ... View more

all experts: would you please help me take a look at my code to see how i can create a subtotal for each table and stack those tables from proc report result, how can i get rid of the second table's title and the 'N', '%' and only have one title and 'N' and  '%' at the top of the table. here is the sas code i am using: ods excel options(sheet_name="test" sheet_interval="none" ) file = "xx\test.xlsx"; proc report data = data.allrsr nowd; column age year,( n pctn) ; define age/group id order=internal; define year/across ; define n / format =8. "N"; define pctn / "%" format =percent7.1;   proc report data=data.allrsr nowd; column raceeth year,( n pctn); define raceeth/group id order=internal; define year/across ; define n / format =8. "N"; define pctn / "%" format =percent7.1; run; ods excel close; the table that i want to create will looks like this: Table 1a. title   2016   2017   2018   2019   2020   N %   N %   N %   N %   N % Age group (yr)                             <13                             13–14                             15–19                             20–24                             25–29                             30–34                             35–39                             40–44                             45–49                             50–54                             55–59                             60–64                             ≥65                             Subtotal                                                           Race/ethnicity                             American Indian/Alaska Native                             Asian                             Black/African American                             Hispanic/Latino a                             Native Hawaiian/Pacific Islander                             White                             Multiple races                             Subtotal                             ... View more