topic How to permanently change the format of a variable in a SAS data set .sas7bdat in SAS Programming

How to permanently change the format of a variable in a SAS data set .sas7bdat

Dublin187 — Sat, 09 Mar 2019 21:27:41 GMT

Hi,

I have a SAS data set .sas7bdat. I am trying to permanently change the format of one of the variables to 7 digits (dropping the 8th one).

I want to try a way to only change the format of that variable without having to upload the whole data set again and without having to change all the subsequent code already written that uses that data set.

The purpose of that manipulation is to merge the data set with another one and 'name' is the common variable so I only need to change the number of digit for the merge to happen.

My code for the merge is in SQL.

data ABC;
set learn.ABC;
informat name $8.;
format name $7.;
run;

I ran the code that appear above, the log didn't report any error , but when I did the proc sql merge, the name variable still had 8 digit.

your help on this little beginner's question would be very helpful

Re: How to permanently change the format of a variable in a SAS data set .sas7bdat

Kurt_Bremser — Sat, 09 Mar 2019 21:36:53 GMT

A format only changes the way a variable is displayed, not its contents.

To remove the 8th position, use the substr() function.

Re: How to permanently change the format of a variable in a SAS data set .sas7bdat

PGStats — Sat, 09 Mar 2019 21:51:58 GMT

SQL join expressions such as a.name=b.name are evaluated on the column values, not the formatted values. If you want the comparison to be based on the shortest string, use a truncated string comparison operator: a.name eqt b.name. If you want the comparison to be based on the first 7 characters, use a substring function: a.name=substr(b.name,1,7)

Re: How to permanently change the format of a variable in a SAS data set .sas7bdat

Dublin187 — Sat, 09 Mar 2019 21:56:39 GMT

thank you for your reply. the substr() statement worked.

however in the next step(in the Proc SQL merge ), the new sas data set isn't being used. Do you know how to make this change permanent in the .sas7bdat file?

Re: How to permanently change the format of a variable in a SAS data set .sas7bdat

PGStats — Sat, 09 Mar 2019 22:08:27 GMT

If your merge is an inner join (and not a left or right join), just keep the other version of the key (the shorter name) in your select clause.

Re: How to permanently change the format of a variable in a SAS data set .sas7bdat

art297 — Sat, 09 Mar 2019 22:18:25 GMT

You've already gotten good answers regarding solving the base problem, however no one has yet mentioned the direct answer to your original question. To change the format, informat and length of variables that already exist in a dataset, the format, informat and/or length statements have to come BEFORE the set statement. e.g.:

data class;
  length name $9;
  format name $9.;
  informat name $9.;
  set sashelp.class;
run;

Art, CEO, AnalystFinder.com

Re: How to permanently change the format of a variable in a SAS data set .sas7bdat

Kurt_Bremser — Sun, 10 Mar 2019 07:11:09 GMT

MERGE happens in data steps, proc sql does JOINS.

Please supply some example data in usable form (data steps with datalines, see my footnotes), and your current merge (or join) code that does not work as expected.

Substr() can be used in the ON or WHERE clauses of SQL joins.