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

Hello, 

I am student and trying to do a project using FDA database called FAERS, the files are in ASCII formats. How can I import these files to SAS UE and then import the data into excel? 

I appreciate your help!

Please see the example in the link below: 

https://fis.fda.gov/extensions/FPD-QDE-FAERS/FPD-QDE-FAERS.html

 

Thanks

1 ACCEPTED SOLUTION

Accepted Solutions
Kurt_Bremser
Super User

See example code for one of the files:

data demo19q1;
infile '$HOME/sascommunity/DEMO19Q1.txt' dlm='$' dsd truncover firstobs=2;
input
  primaryid :$10.
  caseid :$8.
  caseversion
  i_f_code :$1.
  _event_dt :$8.
  @
;
if length (_event_dt) = 4
then do;
  if _event_dt > "19"
  then event_dt = mdy(1,1,input(_event_dt,4.));
  else event_dt = mdy(input(substr(_event_dt,1,2),2.),1,2000+input(substr(_event_dt,3,2),2.));
end;
else if length(_event_dt) = 6
then event_dt = input(cats(_event_dt,'01'),yymmdd8.);
else event_dt = input(_event_dt,yymmdd8.);
input
  mfr_dt :yymmdd8.
  init_fda_dt :yymmdd8.
  fda_dt :yymmdd8.
  rept_cod :$3.
  auth_num :$40.
  mfr_num :$30.
  mfr_sndr :$20.
  lit_ref :$500.
  age
  age_cod :$3.
  age_grp :$1.
  sex :$3.
  e_sub :$1.
  wt
  wt_cod :$3.
  _rept_dt :$8.
  @
;
if length (_rept_dt) = 4
then do;
  if _rept_dt > "19"
  then rept_dt = mdy(1,1,input(_rept_dt,4.));
  else rept_dt = mdy(input(substr(_rept_dt,1,2),2.),1,2000+input(substr(_rept_dt,3,2),2.));
end;
else if length(_rept_dt) = 6
then rept_dt = input(cats(_rept_dt,'01'),yymmdd8.);
else rept_dt = input(_rept_dt,yymmdd8.);
input
  to_mfr :$1.
  occp_cod :$2.
  reporter_country :$2.
  occr_country :$2.
;
format
  event_dt
  mfr_dt
  init_fda_dt
  fda_dt
  rept_dt
    e8601da10.
;
run;

As you can see I had to do quite some cleaning on two of the date columns where all kinds of garbage has been entered. As it is, it still throws two NOTEs, one for a event_dt of "1683" (didn't know that the FDA already worked when the Ottomans besieged Vienna), and the other for a event_dt of "10190205" (now that's history).

You will probably have to do similar cleaning for the other files.

A description of the files is included in the ASC_NTS.pdf file in the zip.

View solution in original post

12 REPLIES 12
Kurt_Bremser
Super User

See example code for one of the files:

data demo19q1;
infile '$HOME/sascommunity/DEMO19Q1.txt' dlm='$' dsd truncover firstobs=2;
input
  primaryid :$10.
  caseid :$8.
  caseversion
  i_f_code :$1.
  _event_dt :$8.
  @
;
if length (_event_dt) = 4
then do;
  if _event_dt > "19"
  then event_dt = mdy(1,1,input(_event_dt,4.));
  else event_dt = mdy(input(substr(_event_dt,1,2),2.),1,2000+input(substr(_event_dt,3,2),2.));
end;
else if length(_event_dt) = 6
then event_dt = input(cats(_event_dt,'01'),yymmdd8.);
else event_dt = input(_event_dt,yymmdd8.);
input
  mfr_dt :yymmdd8.
  init_fda_dt :yymmdd8.
  fda_dt :yymmdd8.
  rept_cod :$3.
  auth_num :$40.
  mfr_num :$30.
  mfr_sndr :$20.
  lit_ref :$500.
  age
  age_cod :$3.
  age_grp :$1.
  sex :$3.
  e_sub :$1.
  wt
  wt_cod :$3.
  _rept_dt :$8.
  @
;
if length (_rept_dt) = 4
then do;
  if _rept_dt > "19"
  then rept_dt = mdy(1,1,input(_rept_dt,4.));
  else rept_dt = mdy(input(substr(_rept_dt,1,2),2.),1,2000+input(substr(_rept_dt,3,2),2.));
end;
else if length(_rept_dt) = 6
then rept_dt = input(cats(_rept_dt,'01'),yymmdd8.);
else rept_dt = input(_rept_dt,yymmdd8.);
input
  to_mfr :$1.
  occp_cod :$2.
  reporter_country :$2.
  occr_country :$2.
;
format
  event_dt
  mfr_dt
  init_fda_dt
  fda_dt
  rept_dt
    e8601da10.
;
run;

As you can see I had to do quite some cleaning on two of the date columns where all kinds of garbage has been entered. As it is, it still throws two NOTEs, one for a event_dt of "1683" (didn't know that the FDA already worked when the Ottomans besieged Vienna), and the other for a event_dt of "10190205" (now that's history).

You will probably have to do similar cleaning for the other files.

A description of the files is included in the ASC_NTS.pdf file in the zip.

ballardw
Super User

@Kurt_Bremser wrote:

 

As you can see I had to do quite some cleaning on two of the date columns where all kinds of garbage has been entered. As it is, it still throws two NOTEs, one for a event_dt of "1683" (didn't know that the FDA already worked when the Ottomans besieged Vienna), and the other for a event_dt of "10190205" (now that's history).

 


Government agencies can be quite astounding in providing services. I have medical testing data where the tests are performed up to 20 years before the subject is born. (date of test and DOB reversed on data entry obviously).

 

And there are a few folks that are scheduled to start services in another government program in 12 years. Which means someone knows that family will have a child under age 5 at that time … .

 

One of the things I like about SAS is relative ease of checking likely erroneous or extremely suspect date values.

ChrisNZ
Tourmaline | Level 20

>  when the Ottomans besieged Vienna

@Kurt_Bremser +1 for the historical reference. We don't have enough of these. Have a croissant on me!

 

Kurt_Bremser
Super User

@ChrisNZ wrote:

>  when the Ottomans besieged Vienna

@Kurt_Bremser +1 for the historical reference. We don't have enough of these. Have a croissant on me!

 


Well, kids in Vienna (and I was one) learn that year in Grammar School. Along with 1529.

RobCo
Fluorite | Level 6

@Kurt_Bremser Thank you so much for your response!

Do you know how i can combine these files? because there are files for drugs name, outcomes and etc

RobCo
Fluorite | Level 6

I am trying to use the code you provided but when i hit the run, it show me blank tables for the variable. 

I have changed "

data demo19q1;
infile '$HOME/sascommunity/DEMO19Q1.txt' dlm='$' dsd truncover firstobs=2;  

to where my files are stored. 

infile '$myfolders/sasuser.v94/DEMO19Q4.txt' ....

but when i run it, it is only a bank table for the defined variables. 

 

Am i doing something wrong? 

Thanks for your help 

RobCo
Fluorite | Level 6

I just could run it successfully with the code you provided! Thanks a lot!! I had to delete the $, so SAS can find the file.

Can I use the same code for other files or do i need to revise the code? 

Unfortunately, i do not know anything about programming or coding!

Could you please help me to import the other files too? 

Thanks 

 

Kurt_Bremser
Super User

You can use the basic structure, but you need to adapt it to the individual file in question. The documentation provided in the first pdf of the archive provides information about the columns.

Thomas11
Calcite | Level 5

Hello,

Thank you for your help, you really help me alot. Would you please provide the SAS codes to import the FDA FAERS ASCII 2019 files (all files), if you have them. I appreciate your help.

Kurt_Bremser
Super User

You can find examples for reading those files in this thread. If your code did not work as expected, show us your code (including the log, if you have ERRORs, WARNINGs or NOTEs you do not understand), and tell us where the results did not meet your expectations.

Use the "little running man" for posting code, and </> for posting logs.

sas-innovate-2024.png

Don't miss out on SAS Innovate - Register now for the FREE Livestream!

Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.

 

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
  • 12 replies
  • 1802 views
  • 8 likes
  • 5 in conversation