I am an intermediate SAS developer who has not had any problems with SAS up until this point where I am now trying to use a database engine.
Objective: Connect to a SQL Server database from within SAS.
Current approach: use SAS ODBC drivers
1. Using SAS onDemand for Academics;
2. Have run PROC SETINIT;
3. SAS/Access to SQL Server not part of the list;
4. Downloaded SAS Deployment Manager;
5. Deployed ODBC drivers for both 64 and 32 bit;
Simple code for testing.
libname sqlsRvr odbc dsn='SS_ODBC',schema='DBO'
SELECT * FROM Main_Disease_tbl;
On Demand does not support any ACCESS module beyond ACCESS to PC Files, IIRC. And you would need to set up the DSN on the On Demand server, not on your PC. Which you can't do in this restricted environment.
You can always export your DB data to text files and upload these to On Demand. This is in fact how it's done at the company I worked for. No SAS/ACCESS license needed, no maintenance of the DB client on the SAS server, and no danger of unexpected loads on the production DB server.
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.