change variable types, formats and informats with values from another table (II)

[Dingdang]

Hi,

this is my second post to this topic . Many thanks again to those who already gave me a lot of suggestions with my last post.

The history is: I have an excel table with around 5000 variables (about 1500 of them are string variables) and i saved it into .txt with tab delimited and used proc import to read the data into SAS. I have another table, Informationtable, which contains 2 columns, the first one lists all the variables and the second one contains the formats that i wish to have for the variables. at the moment the information in the second column is very rough, like "Text" or "Nummer without decimal places" and so on. In the last post the problem was solved by saving all the stringvariables to a macro variable and the formats got corrected with data step. As a result, all the string variables got the same lengh like $300, which for some string variables is indeed a waste of storage place. since my numerical variable all have similar length, i am not very worreid about them.

I've been thinking about Paigemiller's question: Do I really want all the stringvariables to have the same length? It would be much better if this correction happens with more dynamics. Since the data come originally from an excel sheet, its very easy for me to know the maximum length of strings in a column, lets name it length*. So it would be easy to update my informationtable to the following:

Variables    Formats_SAS

Var1          Text with length (length* of Var1)*150%     /*here *150% because this is just the first sample, i would like to leave enough space for the upcoming ones.

                                                                                 (length* of Var1)*150% will be just a number in the excelsheet, like 30  or 1500 or sth. */

Var2          Text with length (length* of Var2)*150%

...

Is it still possible that i use a macro to adjust the length of the string variables with the values unter Formats_SAS?

This is not an urgent question. I think i can live with my old solution. Aber I am really curious if this dynamics can work. Then it will be much easier for me to read excel data correctly into SAS in the future.

BR  Dingdang

[data_null__]

Perhaps the easiest thing to do is measure and trim. https://communities.sas.com/message/163032#163032

[ballardw · Accepted solution]

If you can get the variables and desired lengths with one variable and the length you want it is very easy to use Excel formulae to create a third column with the format statement it would look something like:

assuming column A has the variable name and B the length then

= "Format "&A1&" $"&B1".;"

You can copy and paste the result column into the SAS editor.

You could also have a column to modify the names to something shorter for SAS to use and reference that column in the formula.

Current variable               length     Newvariable    

Respondent First Name      25          FirstName

The NewVariable column would then also be used for an INPUT statement.

with a second formula column you could also create SAS label statements for the NewVariable using the text from your current variable name.

[Dingdang]

super, this is a good idea