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

[Dublin187]

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

[Kurt_Bremser]

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

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

[Dublin187]

Hi

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?

[PGStats]

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.

[Kurt_Bremser]

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. 

[PGStats · Accepted solution]

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)

[art297]

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