When I added the Let option to the proc transpose, I got the following:   WARNING: 4 observations omitted due to missing ID values. NOTE: There were 99187 observations read from the data set WORK.CLEANY. NOTE: The data set WORK.WANTY has 26568 observations and 38 variables.   The same number of observations as the proc sql. Any thought on this?   ... View more

Thank you for all your quick and helpful responses. The proc transpose threw up some error messages saying ' The ID value "FL" occurs twice in the same BY group'.   The PROC SQL worked fine for this purpose. I didn't try the second suggested SQL procedure since the first worked for my data.   proc sql; select distinct state into :state separated by ' ' from have; quit; %put &state; data want1; do _n_=1 by 1 until(last.id); set have; by id; array st &state; if _n_=1 then call missing(of st(*)); do _i_=1 to dim(st); if vname(st(_i_))=state then st(_i_)=counts; end; end; run; I appreciate your efforts. Thank you all once again. ... View more