SAS Programming

DATA Step, Macro, Functions and more
BookmarkSubscribeRSS Feed
☑ This topic is solved. Need further help from the community? Please sign in and ask a new question.
SASuser4321
Obsidian | Level 7

The objective of my code is to display the type, number of distinct values, and the number of missing values. And at this point, I'm very close to getting it. In my _smmry_ table, you can see the number of missing values for all the variables. However, by the time I'm done with my left join, the table final_Alpha_ doesn't display the number of missing values for two of the variables and instead just shows ".". What went wrong in the left join? Help would be appreciated. Thank you.

 

 

%let set=sashelp.cars;

    proc transpose data=&set(obs=0) out=names;
       var _all_;
       run;
    proc sql;
       select cats('_',_name_,'=missing(',_name_,');') into: stmts separated by ' ' from names;
       run;
    
    data missing;
       set &set;
       &stmts;
       keep _:;
       run;
    proc summary data=missing;
       var _numeric_;
       output out=smry sum=;
       run;
    proc transpose data=smry(drop=_type_ _freq_) out=smry_;
       run;

data _smry_(drop =  _name_);
set smry_;
name=compress(_name_, , 'kas');
run;

proc sort data=_smry_;
    by name;
run;

proc contents data=&set out=con_(rename=(name=TableVar));
run;

proc freq nlevels data = &set;
ods output nlevels=distinct;
run;

proc sort data=distinct;
    by TableVar;
run;
data _smmry_(rename=(name= TableVar col1 = miss));
retain name col1;set _smry_;run;

data final_Alpha (keep=TableVar distinct_ TYPE );
    merge distinct(rename=(Nlevels=distinct_)) con_(keep=TableVar type) ;
    by TableVar;
run;

proc sql;
create table final_Alpha_ as select * from final_Alpha as x
left join  _smmry_ as y
ON x.TableVar=y.TableVar;quit;

proc sort data=final_Alpha_ out=_final_Alpha;
by descending miss;
run;

proc freq nlevels data=&set;
tables _all_;
run;

 

1 ACCEPTED SOLUTION

Accepted Solutions
PeterClemmensen
Tourmaline | Level 20

The problem is when you remove the underscore from the name variable and create the name variable. You remove all underscores (not jsut the first one). Therefore, _MPG_City becomes MPGCity. Try something like below. The rest of your code seems fine.

 

data _smry_(drop =  _name_);
set smry_;
name = substr(_name_, 2);
run;

 

View solution in original post

1 REPLY 1
PeterClemmensen
Tourmaline | Level 20

The problem is when you remove the underscore from the name variable and create the name variable. You remove all underscores (not jsut the first one). Therefore, _MPG_City becomes MPGCity. Try something like below. The rest of your code seems fine.

 

data _smry_(drop =  _name_);
set smry_;
name = substr(_name_, 2);
run;

 

sas-innovate-wordmark-2025-midnight.png

Register Today!

Join us for SAS Innovate 2025, our biggest and most exciting global event of the year, in Orlando, FL, from May 6-9. Sign up by March 14 for just $795.


Register now!

How to Concatenate Values

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.

SAS Training: Just a Click Away

 Ready to level-up your skills? Choose your own adventure.

Browse our catalog!

Discussion stats
  • 1 reply
  • 368 views
  • 1 like
  • 2 in conversation