New to SAS. I'm updating an old report. I need to reshape the data a certain way so it can be uploaded/consumed. Would like to avoid going higher up through the process to rectify this as it will probably change the values.
In a nutshell, is there a way to insert a line with zero values for the variables, for groups missing a certain type.
I.e. i want all vehicle type groups to have all 3 types (car, van, suv). at this point, it could even be a proc print step if that's possible.
Thanks! Sorry for the brevity.
So you want output to be a dataset or a txt file ?
data have;
input sec $ id $ fr_m fr_f tot_;
cards;
06 M 10 8 0
06 D 0 12 120
06 To 8 18 20
06 PT 3 7 9
06 FT 53 41 9
06 To 7 79 13
;
run;
proc iml;
use have;
read all var _num_ into x;
close;
want=rowvec(x);
create want from want;
append from want;
close;
quit;
proc export data=want outfile='c:\temp\outfile.txt' dbms=tab replace;run;
PROC MEANS in combination with the COMPLETETYPES and PRELOADFMT options should provide what you want. Check out this SAS doc example.
https://blogs.sas.com/content/iml/2024/01/08/unobserved-levels-categorical.html
That would be better if you post some data.
Here is an example:
data have; set sashelp.class; run; proc sql; create table want as select a.*,coalesce(weight,0) as weight,coalesce(height,0) as height from (select * from (select distinct sex from have),(select distinct age from have)) as a natural left join (select sex,age,sum(weight) as weight from have group by sex,age) natural left join (select sex,age,sum(height) as height from have group by sex,age) ; quit;
Thanks for the responses so far. Let me explain this another way. The spreadsheet visual I included before is accurate of the data. It's a comprehensive table of obs that has been grouped by vehicle type. What i'm trying to add is
'for any grouping (car_type) i want it to have (car, van, suv), if van is missing, insert it' so ALL groupings have car, van, suv - in that order. If the var values are empty, that's fine i can handle that with missing option.
Hi @btbell,
I had a similar (not exact) problem in the past and Community Experts (Ksharp) recommended to use SPARSE option in proc freq step which generates extra records for missing categories (per group) with 0 values.
Below is an example and hope this helps you to figure out your solution.
*Create data HAVE;
data have;
set sashelp.cars;
if upcase(substr(make, 1, 1)) in ('A' 'B' 'C' 'D' 'E') then make= 'Var1';
else if upcase(substr(make, 1, 1)) in ('F' 'G' 'H' 'I' 'J') then make= 'Var2';
else if upcase(substr(make, 1, 1)) in ('K' 'L' 'M' 'N' 'O') then make= 'Var3';
else make= 'Var4';
run;
*Calculate frequency counts for all categories, and creating null records for missing categories;
proc freq data= have noprint;
tables origin*make*type/out=have_wide missing sparse;
run;
*Transposing the results to get the desired table structure;
proc sort data=have_wide; by origin type make; run;
proc transpose data=have_wide out=want (drop=_:);
by origin type;
var count;
id make;
run;
As you see in the result, we have all types per ORIGIN, even though it did not exist in the source data.
Hi @A_Kh
Thanks for you all's help. I've probably made things more complicated by the fact that i'm so new to SAS, to say there's a gap in my knowledge at this point is an understatement. There are so many ways this could probably be handled so let me start with a different question based on the data below - can i get all obs's numerical values onto one line (obs) in that order? The orig vars can be replaced and made to simply be var1...var18 ( i won't need the sec or id)
data have; input sec $ id $ fr_m fr_f tot_; cards; 06 M 10 8 0 06 D 0 12 120 06 To 8 18 20 06 PT 3 7 9 06 FT 53 41 9 06 To 7 79 13 ; run;
Want:
I need this out on a single line, e.g.
10 8 0 0 12 120 8 18 20 3 7 9 53 41 9 7 79 13
so my text file would be
data _null_; set have; file "outfile.txt"; put @1 "xxxx" @5 "yyy" @7 "zzz" @11 var1 @16 var2 @21 var3 @26 var4 @31 var5 @36 var 6 @41 var 7 @46 var 8 @51 var9 @56 var10 @61 var11 @66 var 12 @71 var 13 @76 var 14 @81 var 15 @86 var @91 var 16 @96 var 17 @101 var 18 run;
Thanks for your patience and any help is appreciated.
So you want output to be a dataset or a txt file ?
data have;
input sec $ id $ fr_m fr_f tot_;
cards;
06 M 10 8 0
06 D 0 12 120
06 To 8 18 20
06 PT 3 7 9
06 FT 53 41 9
06 To 7 79 13
;
run;
proc iml;
use have;
read all var _num_ into x;
close;
want=rowvec(x);
create want from want;
append from want;
close;
quit;
proc export data=want outfile='c:\temp\outfile.txt' dbms=tab replace;run;
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.