Convert all character variables to numeric and use the same variable names in the output data se

[Anita_n]

I need to test the methods patiently to see which works for me. I will let you know then when am done.

Thankyou both for the help.

[Anita_n]

@ballardw @Tom @PaigeMiller 

Thanks to you all for the help. I have finally imported the data into to SAS correctly using datastep. This previously did not work because the DB wasn't clean. For example numeric fields had also text values. 

I also realised that the main problem, was the order of the variables in the datastep.

It didn't match the order of the variables in the csv file, so this seemed to have confused SAS, that was why the output wasn't correct.

There were some other small issues but I have been able to resolve them. 

This was very time consuming due to the large number of variables in the file but am happy, my work wasn't in vain

[ballardw]

Maybe it is worth discussing prevention vs fixing.

WHY are all the variables you want as numeric currently character? If this was the result of an incorrect file import then perhaps the better method would be to correct the read step instead of fixing it afterwards.

Or possibly even EXPORT the data to a text formatted file and write a data step to read that back in with the desired properties.

One moderately frequent cause of variables that should be numeric coming into SAS as character is use of Proc Import with a file that uses some sort of text like NULL or NA for what should be missing values. That can be dealt with using a custom informat and data step to read the values the first time around.

[Anita_n]

The data comes from Postgresql and contains 1634 variables and 1985 observations. The variable names are very long and have special characters. I have tried all means to use sas to import but no way. 

I ended up using stattransfer to import this to sas. I realise that, all my num vars are converted to char. Dates to chars. For the issue with the dates I have been able to solve it. some decimal values like 4.2 are converted to 4.Feb

It isn't easy to edit this file, even though it's a delimited file (semicolon seperated)

the most tedious part are the long varnames

[Tom]

You can connect SAS to Postgres.  It will require at least SAS/Access to ODBC license.

If the variable names are longer than 32 bytes then you will have issues.  Same with table (dataset) names. But you can work around that with pass thru code so they rename them on the remote side before pulling them into SAS.

If you have values being converted to dates then you have probably accidentally opened a delimited file with Excel, I doubt that STAT/Transfer would do that itself.  Do NOT let EXCEL automatically open a delimited  file.  If you do open it with Excel make sure to do it under control so that you can tell it how to treat each column in the delimited file.

If you have a delimited file then read it directly with SAS data step. Then you have total control over the variable names, type and length.  You can control how the text in the file is converted to the values stored in the SAS variables. You can control what SAS formats are attached the variables (if there is any need to attach any formats to any of the variables).

[Anita_n]

Yes, you are right but in my case the data step does work properly because of the problem with the variables. I cannot connect directly to postgresql because the data is from an external body who works with postgresql. I receive just the delimited file from them

[Tom]

---

@Anita_n wrote:

Yes, you are right but in my case the data step does work properly because of the problem with the variables. I cannot connect directly to postgresql because the data is from an external body who works with postgresql. I receive just the delimited file from them

---

So just read the delimited file directly using a data step.  Although you might need to add some pre-processing to the delimited file to get it into shape for reading by SAS.  Things to watch out for

• embedded end-of-line characters in the middle of character values in the file. This will make the row look like multiple rows to SAS
• embedded delimiters in character values.  SAS expects such values to be enclosed in quotes.  Some systems will instead insert a backslash, \ , to "protect" or "escape" the special meaning of that character.  Some even forget to do anything to protect them.
• embedded quotes in character values.  SAS expects such values to be enclosed in quotes and the actual quotes doubled up.
• The use of text like NA or NULL instead of just not having any text between the delimiters to indicate missing values.

[ballardw]

---

@Anita_n wrote:

Yes, you are right but in my case the data step does work properly because of the problem with the variables. I cannot connect directly to postgresql because the data is from an external body who works with postgresql. I receive just the delimited file from them

---

Please show us the data step code you have attempted.

Clearly describe the "problem" with the variables. If you are getting invalid data messages from SAS, provide some of those. Typically those are resolved with a proper Informat or Infile setting.

Any delimited file you can specify the variable names you want when you read it with a data step.

I have one source that provides files with 70+ character column headings.So I use a shorter name that makes sense and provide Labels for the variables that look somewhat like the headings only with better spelling and sentence case. (as well as use the opportunity to throw away the 80 percent of variables that my projects do not need)