topic Re: Dealing with a variable with two different formats in one (date and character) in SAS Programming

Dealing with a variable with two different formats in one (date and character)

sks521 — Mon, 17 Jun 2019 10:03:09 GMT

Hi,

I am doing a many-to-one merge of two data sets. one is the source data set and the other has two variables; id to merge on and the other one 'Date' which is in below format in excel sheet;

21/07/2017

Null

08/02/2018

Null

So when I am merging the two data sets, the values I am seeing in the merged set for 'Date' are like;

42937

Null

42971

Null

Can someone help with writing the right code please?

Thanks

Re: Dealing with a variable with two different formats in one (date and character)

sks521 — Mon, 17 Jun 2019 10:12:41 GMT

SAS log is also giving me this message;

'One or more variables were converted because the data type is not supported by the V9
engine. For more details, run with options MSGLEVEL=I.'

Thanks

Sarwat

Re: Dealing with a variable with two different formats in one (date and character)

PeterClemmensen — Mon, 17 Jun 2019 10:19:21 GMT

Simply use the ddmmyy10. format like

format date ddmmyy10.;

Re: Dealing with a variable with two different formats in one (date and character)

Kurt_Bremser — Mon, 17 Jun 2019 10:22:16 GMT

Start by getting rid of Excel files as a data source. Save the spreadsheets as csv files and read those with data steps; this will give you control over the whole process, so you can properly set variable attributes.

While fixing your process this way, you should post your merge code and the log from it. Posting example data in data steps with datalines will also be helpful.

Re: Dealing with a variable with two different formats in one (date and character)

sks521 — Mon, 17 Jun 2019 10:22:28 GMT

Thanks, where should I use this command, in merging syntax?

Re: Dealing with a variable with two different formats in one (date and character)

sks521 — Mon, 17 Jun 2019 10:25:26 GMT

Thanks but how to save multiple worksheets as csv?

Re: Dealing with a variable with two different formats in one (date and character)

sks521 — Mon, 17 Jun 2019 10:28:32 GMT

And if I use it in the proc import step, I get this error message;

NOTE: VARCHAR data type is not supported by the V9 engine. Variable DateIntStarted has been
converted to CHAR data type.
ERROR: You are trying to use the numeric format DDMMYY with the character variable
DateIntStarted in data set WORK.GPDATA.

Re: Dealing with a variable with two different formats in one (date and character)

sks521 — Mon, 17 Jun 2019 10:44:07 GMT

So now I have created separate csv for each excel worksheet, imported them into SAS and merging went fine with one date variable having both var and char formats in one. I hope I am on the right track!

Thanks for all your help.

Re: Dealing with a variable with two different formats in one (date and character)

Kurt_Bremser — Mon, 17 Jun 2019 10:51:00 GMT

@sks521 wrote:

Thanks but how to save multiple worksheets as csv?

File - Save as, as usual. If you are concerned about the workload, just take into account the troubles you have right now.

A sustainable data handling process needs to keep Excel files out of the loop completely.