BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
Jedrzej
Obsidian | Level 7

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 ?

 

 

1 ACCEPTED SOLUTION

Accepted Solutions
Oligolas
Barite | Level 11

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 -

View solution in original post

9 REPLIES 9
LinusH
Tourmaline | Level 20

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.

Data never sleeps
Jedrzej
Obsidian | Level 7
example

format of variable abc in set a is decimal

variable abc in set in set b is integer.

now i want variable abc in set a to be alson an integer.

RW9
Diamond | Level 26 RW9
Diamond | Level 26

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.

Kurt_Bremser
Super User

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.

Oligolas
Barite | Level 11

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 -

Ksharp
Super User
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
Obsidian | Level 7
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
Kurt_Bremser
Super User

@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.

Kurt_Bremser
Super User

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.

sas-innovate-2024.png

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.

 

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 9 replies
  • 2010 views
  • 2 likes
  • 6 in conversation