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

Hi,

this is my second post to this topic Smiley Wink. 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

1 ACCEPTED SOLUTION

Accepted Solutions
ballardw
Super User

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.

View solution in original post

3 REPLIES 3
ballardw
Super User

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.

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
  • 3 replies
  • 804 views
  • 0 likes
  • 3 in conversation