Hi All,
My code:
PROC SQL;
SELECT Neighborhoods, COUNT(childid) AS NewEnroll_ByCou_YTD
FROM counts
where '01jan2016'd <= dateeval <= &Marco_tdate
group by Neighborhoods;
QUIT;
Gives me the output below. However, there are 35 other cateorgies that are associated with this variable. Is there a way to have them all listed? So in total it would be 42 rows, All the neighborhoods listed and zeros?
Central Bronx | 2 |
Bronx Park and Fordham | 3 |
High Bridge and Morrisania | 4 |
Hunts Point and Mott Haven | 2 |
Central Harlem | 10 |
Chelsea and Clinton | 1 |
East Harlem | 42 |
Not within Proc SQL, easily.
Look up PRELOADFMT and proc tabulate. You can also try proc freq with sparse option.
Hello,
With proc sql, maybe sometghing like this :
PROC SQL;
SELECT Neighborhoods, COUNT(childid) AS NewEnroll_ByCou_YTD
FROM counts
where '01jan2016'd <= dateeval <= &Marco_tdate
group by Neighborhoods
UNION
SELECT DISTINCT Neighborhoods, 0 AS NewEnroll_ByCou_YTD
FROM counts
WHERE dateeval<'01jan2016'd OR dateeval>&Marco_tdate;
QUIT;
Not an elegant solution but create a base data set with the values for the variable and a count of 0.
Create a dataset from your counting code.
The create a report data set by updating the value in the base set from the counting set.
But for a report I would use @Reeza's Proc tabulate approach, especially if I'm doing this very often.
data levels;
sex='F';output;
sex='M';output;
sex='X';output;
run;
data class;
set sashelp.class(in=ina) levels;
w=ina;
run;
proc freq data=class;
table sex;
weight w/zeros;
run;
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.
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.