hi,
i have two data sets
set a
set b
they have the same variables but different formats values. Is there an easy way to make for example formats in set b the same as in set a ?
Hi,
if your columns are same in both datasets try this:
/*create test data*/
proc format; value $ gender 'M'='Male' 'F'='Female' ; run; data test1; set sashelp.class; format sex $gender.; run; data test2; set sashelp.class; run; data vcolumn; set sashelp.vcolumn; where libname eq 'WORK' and memname eq 'TEST1'; run;
/*test code*/ data _NULL_; set vcolumn end=last; if _N_ eq 1 then call execute('data test2; set test2;'); if not missing(format) then call execute('format '||strip(name)||' '||strip(format)||';'); if last then call execute('run;'); run;
- Cheers -
Not sure exactly what you mean. Do you want to merge the format domain values, or use formats from b instead of in a?
Give a simple example to illustrate your problem.
Whilst you have some examples of how to do it technically, I would question why you need to do this. I mean if you have decimal values in a dataset, is it really a good idea to be displaying anything other than decimal - proc report output and such like would use the formatted values, but code wouldn't so you may end up with discrepantions and confusion. I would fix why exactly this became an issue in the first place. I have a fiver says this is linked to importing Excel files as normal.
You can automate such a process by using dictionary.columns and a data step with call execute:
data work.class;
set sashelp.class;
run;
%let library=work;
%let col=age;
%let form=z10.;
proc sql;
create table control as
select memname from dictionary.columns
where upcase(libname) = upcase("&library.") and upcase(name) = upcase("&col.");
quit;
data _null_;
set control end=done;
if _n_ = 1
then call execute("proc datasets library=&library. nolist;");
call execute('modify ' !! trim(memname) !! ';');
call execute("attrib &col. format=&form.;");
if done then call execute('run;quit;');
run;
You will see that dataset WORK.CLASS now displays the age with leading zeroes.
Hi,
if your columns are same in both datasets try this:
/*create test data*/
proc format; value $ gender 'M'='Male' 'F'='Female' ; run; data test1; set sashelp.class; format sex $gender.; run; data test2; set sashelp.class; run; data vcolumn; set sashelp.vcolumn; where libname eq 'WORK' and memname eq 'TEST1'; run;
/*test code*/ data _NULL_; set vcolumn end=last; if _N_ eq 1 then call execute('data test2; set test2;'); if not missing(format) then call execute('format '||strip(name)||' '||strip(format)||';'); if last then call execute('run;'); run;
- Cheers -
I don't understand. Once you set them together, the table b will follow the a's format . You don't need to do anything . data a; set sashelp.class; keep weight; format weight f10.2; run; data b; set sashelp.class; keep weight; format weight f10.; run; data want; set a b; run;
@Jedrzej wrote:
but also there are variables with different types : in set a var is numeric in set b var is char so setting them won't work
Then you need to align your variable types first. You can't have the same format for variables of different type, formats are either for numeric (no prefix) or character (prefix $) variables.
PS the fact that you not only have different formats, but also have incompatible types lets me suspect that your ETL process is faulty and probably based on using proc import, which is not suited for production work.
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.