BookmarkSubscribeRSS Feed
okcken
Calcite | Level 5

Hello,

I am trying to do seamless export of data from a database to SAS. I am trying to follow the Redcap model of downloading three files: SAS file, Pathway mapper and CSV file. Does anyone in the community know how to go about implementing this?

Sorry, if I am asking this question in the wrong discussion community. If I am, please direct me to the right group.

Thank you!

Ken

7 REPLIES 7
SteveDenham
Jade | Level 19

Ken, try posting to the SAS Procedures Forum.

Steve Denham

Doc_Duke
Rhodochrosite | Level 12

Ken,

What do you mean by 'seamless'?  The RedCap instructions are pretty straightforward.

I dislike the *.SAS file that redcap creates (why does every character variable need to be 500 characters wide -- that is one lazy developer!), so I had to write follow-on code to restructure the data I wanted to keep.

Doc Muhlbaier

okcken
Calcite | Level 5

Thanks, Doc Muhlbaier!

I know the instructions are very straightforward. However, I was trying to create a similar process for another application not Redcap. I know I can give access to the backend (SQL Server, MySQL) using the SAS/ACCESS component. I was also looking for an alternative method other than SAS/ACCESS. This has been a very helpful community. Thanks for all your input.

Ken

Ksharp
Super User

SQL Server, MySQL   --> CSV file or other txt file --> SAS  ,  vice versa .

Delgoffe_MCRI
Fluorite | Level 6

I have recently published a macro to export REDCap data into SAS along with formats and a data dictionary. Please see my paper, presentation, and code by filling out this survey: https://redcap.link/REDCapIsMySASFriend.

 

Distribution using this method allows me to distribute only the most recent version, so you can check back to this survey in the future as well.

Tom
Super User Tom
Super User

Thanks.

A quick glance at the code and the main benefit of the macros seems to be to automate the API call to retrieve the data dictionary and the data.  So that you could initiate an extract from RedCap via code instead of the user interface.

 

I noticed you did include a simplified method of checking the downloaded CSV file to insure that there are no embedded CRLF pairs in any of the variable values.

 

Are there any other smarts in the macro also that make working with the format that RedCap exports the data easier?  That is understanding the meaning of the data dictionary and using that to convert the data as exported into a structure more suitable for working with in SAS? 

 

 

Delgoffe_MCRI
Fluorite | Level 6

Yes, it uses regular expressions to remove HTML, carriage returns, and completely parses the REDCap data dictionary to provide labels and formats to the SAS dataset it creates.

 

At times you may need to control how it interprets the type of variable it makes based on existing data (ESPECIALLY with missing data codes), but the next version under development will go through and replace text missing data codes with numeric acceptable values before import to avoid that...so stay tuned!

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

What is ANOVA?

ANOVA, or Analysis Of Variance, is used to compare the averages or means of two or more populations to better understand how they differ. Watch this tutorial for more.

Find more tutorials on the SAS Users YouTube channel.

Discussion stats
  • 7 replies
  • 3503 views
  • 0 likes
  • 6 in conversation