How can I import ASCII files (from FAERS database, FDA database) into SAS UE?

[RobCo]

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

[Kurt_Bremser · Accepted solution]

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]

---

@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]

>  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]

---

@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]

@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

[Kurt_Bremser]

You need to look for common variables that can be used as keys in joins, or for lookups with formats or hash objects.

[RobCo]

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 

[Kurt_Bremser]

What does the log say? Please post it, using the {i} button.

[RobCo]

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]

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]

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]

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.