BookmarkSubscribeRSS Feed
alotaibifm
Calcite | Level 5

The files are CSV format. I don't think the problem in the file itself. I just need to know if it's possible to match dates, if yes, what is the appropriate codes. Thanks, 

Reeza
Super User
There's nothing wrong with the code, the problem is with the data.
alotaibifm
Calcite | Level 5

can I change the date format from the original file (excel file)? will that help? 

Reeza
Super User
Nope, your best bet is to import ID as character and ensure that dates are imported as a SAS date and then try merging it. Make sure to import your data using a data step (not proc import) and verify your types/values.
VDD
Ammonite | Level 13 VDD
Ammonite | Level 13

Do the changing in SAS so that you don't have to manually do it in Excel again.

Code once run many.

 

Reeza
Super User
And you can get rid of decimals with the round() function. Note that although the name is the same as Excel it operates a little differently so read the docs.
alotaibifm
Calcite | Level 5

I have 5 files for the (ICD_Codes), so I need to truncate them as one file. when I do that, the dates get messed up. I don't know why this is happening. see the attached picture (last column). 

 

then I did this code 

data DSNEW;
set dss5;
D_DATE= input(DIAG_DT_TM,anydtdte32.);
Format D_DATE mmddyy10.;
drop DIAG_DT_TM;
run;

 

to fix it, and maybe I messed it up. the code was only applied for one dataset and I changed the rest by changing the excel date format. 

Reeza
Super User
I stand by my previous response, fix the data import to avoid all of this. Feel free to post that code and we can help fix it if needed.

Since that variable has dt_tm I would have assumed it was a date time, not a date value. If that’s the case, try anydtdtm instead of anydtdte.
alotaibifm
Calcite | Level 5

I just want to keep only the date. I'm not interested in the time. Can I keep only the date? 

Reeza
Super User

@alotaibifm wrote:

I just want to keep only the date. I'm not interested in the time. Can I keep only the date? 


Sure, use DATEPART() after on the variable to get just the date portion. 

or TIMEPART() for the time portion.

alotaibifm
Calcite | Level 5

data New_All;
set EALLD_NEW;
D_DATE= input(DIAG_DT_TM,anydtdte32.);
Format D_DATE DATE9.;
drop DIAG_DT_TM;
run;

 

I did it all over again, and I'm afraid that I will mess up the whole data. I reimported the CSV files through (file>import> etc) then I concatenate all the 5 files in one file. Now, I want to change the date format, so that I can merge them correctly. When I applied the code I posted here, the date shift to 5 years or so, it's not giving me the correct conversion. Do you think it's a correct code? 

Reeza
Super User
I reimported the CSV files through (file>import> etc)

You really need to import the files with a data step, not an automated import process. Otherwise you'll likely still run into the same issues.
alotaibifm
Calcite | Level 5
Honestly, I have never imported a data through (data step). I will try to read and find resources to explain the process in detail. If you have any resources, I would appreciate your help with that too.

Do I need to import the (demographic file), which has date as well through data step as well??
Reeza
Super User
Run proc import. Get the code from the log.
Paste it in, and go through and verify that each field is being read correctly.When doing the PROC IMPORT use GUESSINGROWS=MAX to ensure the best results, but you will still need to manually verify the data. If you have any documentation on the data, or a record layout file you should compare it against that to ensure it's read properly.

Ultimately, remember, garbage in= garbage out.

https://stats.idre.ucla.edu/sas/faq/how-do-i-read-in-a-delimited-ascii-file-in-sas/
alotaibifm
Calcite | Level 5

I imported the 5 files by (proc import) here is the code for one file: 

proc import
out=library.E1
datafile='C:\Users\alotaibifm\Desktop\Study 3/export1.csv'
dbms=csv
replace;
GUESSINGROWS=MAX;
run;

 

when I tried to concatenate all 5 files in one file, SAS gave me this error:

4027 data library.Dall;
4028 set library.E1 library.E2 library.E3 library.E4 library.E5;
ERROR: Variable DIAG_DT_TM has been defined as both character and numeric.
ERROR: Variable DIAG_DT_TM has been defined as both character and numeric.
4029 run;

 

then I tried to convert the date by this code and gave me a lot of missing value. 

data library.E11;
set library.E1;
Visit_Date= input(DIAG_DT_TM,anydtdte32.);
Format Visit_Date mmddyy10.;
drop DIAG_DT_TM;
run;

Ready to join fellow brilliant minds for the SAS Hackathon?

Build your skills. Make connections. Enjoy creative freedom. Maybe change the world. Registration is now open through August 30th. Visit the SAS Hackathon homepage.

Register today!
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
  • 40 replies
  • 1240 views
  • 6 likes
  • 4 in conversation