turn on suggestions

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

Find a Community

- Home
- /
- SAS Programming
- /
- Base SAS Programming
- /
- change variable types, formats and informats with ...

Topic Options

- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

11-13-2013 03:38 AM

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

Accepted Solutions

Solution

11-13-2013
10:54 AM

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

11-13-2013 10:54 AM

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.

All Replies

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

11-13-2013 06:27 AM

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

Solution

11-13-2013
10:54 AM

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

11-13-2013 10:54 AM

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.

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

11-14-2013 02:19 AM

super, this is a good idea