About SASJedi

SASJedi · ‎09-03-2021

Thanks for the explanation!

SASJedi · ‎08-30-2021

I recommend using PROC CPORT / PROC CIMPORT instead: /* Create some empty data sets for testing */ data work.one work.two work.three; stop; length text $1 number 8; run; /* Prove tables exists with no rows */ proc sql; select MEMNAME, NVAR, NLOBS from dictionary.tables where memname in ("ONE","TWO","THREE"); quit; /* Export the empty datasets */ filename xport "c:\temp\my_transport_file.xpt"; proc cport library=work file=xport memtype=data; run; filename xport clear; /* Clear out all datasets in the WORK library */ proc datasets library=work kill nolist nodetails; run; From the Results tab: Member Name Number of Variables Number of Observations ONE 2 0 THREE 2 0 TWO 2 0 Now, re-import the data: /* Import the empty data sets */ filename import "c:\temp\my_transport_file.xpt"; proc cimport library=work file=import; run; /* Prove tables exists with no rows */ proc sql; select MEMNAME, NVAR, NLOBS from dictionary.tables where memname in ("ONE","TWO","THREE"); quit; From the Results tab: Member Name Number of Variables Number of Observations ONE 2 0 THREE 2 0 TWO 2 0 This should wor better for you.

SASJedi · ‎08-19-2021

This worked for me: Setup some test data: data have; infile datalines dsd dlm='|'; input start_date:date9. end_date:date9. title:$50.; format start_date end_date date9.; datalines; 01aug2021 | 01oct2021| 2021 Batch 1 01jul2021 | 01oct2021| 2021 Batch 2 01jun2021 | 01oct2021| 2021 Batch 3 ; libname db oracle user=student pw=Metadata0; data db.Titles; if _n_=1 then call streaminit(12345); set have; output; do i= 1 to rand('integer',1,5); start_date=sum(start_date,rand('integer',1,5)); end_date=sum(start_date,rand('integer',1,3)); output; end; drop i; run; Then this SQL join did the trick: proc sql; select db.* from work.have as h inner join db.Titles as db on db.title=h.title and datepart(db.start_date)=h.start_date and datepart(db.end_date)=h.end_date ; quit; START_DATE END_DATE TITLE 01AUG2021:00:00:00 01OCT2021:00:00:00 2021 Batch 1 01JUL2021:00:00:00 01OCT2021:00:00:00 2021 Batch 2 01JUN2021:00:00:00 01OCT2021:00:00:00 2021 Batch 3

SASJedi · ‎08-19-2021

I think the problem is this: select '_1' as VAR, '0' as CHAR, count(distinct PATID) as CNT from working where not missing(MAECAREI) union select '_1' as VAR, MAECAREI as CHAR, count(distinct PATID) as CNT from working group by MAECAREI Because the first select created the value for the column named CHAR as '0', this is a character value. From the error, it appears that MAECAREI is a numeric value. YOu can't "stack" a numeric value in a character column. Try this instead: select '_1' as VAR, 0 as CHAR, count(distinct PATID) as CNT from working where not missing(MAECAREI) union select '_1' as VAR, MAECAREI as CHAR, count(distinct PATID) as CNT from working group by MAECAREI

SASJedi · ‎08-19-2021

Once again, what version of SAS are you using? Are you working with SAS at work? Or SAS University Edtion? Or SAS On-Demand for Academics?

SASJedi · ‎08-16-2021

When you SAS is running in the operating system installed on your PC, any file you can find via the Explorer in SAS Studio are also avialable to your SAS Compute Server (the SAS installed on your machine): With SAS University Edition(UE), your SAS is running in a Linux virtual machine. This works just as if your Linux SAS Compute Server was installed on completely different hardware. The Virtual machine has virtual disks, but you don't have write access to those. Your computer has a hard drive, but the SAS University Edition virtual machine knows nothing about that. When you set up SAS University Edition, you were given instructions on setting up a shared folder - a location where both the Virtual Machine and your Windows computer could read and write. Because SAS University Edition is deprecated, I don't have access to it, so I can't give you the exact path, but you need to put the .dat file in there and use the Linux path to point to the file.

SASJedi · ‎08-16-2021

You will find that the macro IN operator will fail and throw an error if a NULL value is presented to the operator on either side. The way your macro is constructed, you are expecting either a single value in countrym or a list in countrylistm. This means that one of those values will always be NULL. In the body of the macro, both values are always tested with an IN operator, so the IN operator is always exposed to a NULL value. Hence the errors you are seeing. May the SAS be with you! Mark

SASJedi · ‎08-12-2021

The SAS code provided is incomplete and includes non-printable characters. To create this dataset: 1. Right-click the link for the .DAT file and choose "Save link as" to save the adult.dat file to your hard drive. I saved mine to the C:\temp directory: 2. Right-click the SAS Code link and save the adult.sas program file, too. 3. Open the adult.sas program file in SAS and make the following changes: The first part of the program looks like this: FILENAME ADULT "D:\Questionnaire\DAT\ADULT.DAT" LRECL=3348; *** LRECL includes 2 positions for CRLF, assuming use of PC SAS; DATA WORK; In the first line, change the FILENAME statement to point to the actual location of the adult.dat file. And wanted my dataset to be named adult instead of work, so I also changed the name of the output file in the DATA statement: FILENAME ADULT "C:\temp\ADULT.DAT" LRECL=3348; *** LRECL includes 2 positions for CRLF, assuming use of PC SAS; DATA WORK.ADULT; Now, use go to the very end of the program file and you'll see something like this: HAZNOK5R = "Number of BP's used for average K5"; The little box you see is a non-printable character. Delete it and replace it with a RUN; statement so it now looks like this: HAZNOK5R = "Number of BP's used for average K5"; run; Save the modified SAS program, then run it. It should work fine now.

SASJedi · ‎07-29-2021

I do this enough to have a macro for it. There are some dependencies on other macros I've written. You can try it yourslef : /* Get the necessary macro files */ filename getmacro url "https://raw.githubusercontent.com/SASJedi/sas-macros/master/exist.sas"; %include getmacro; filename getmacro url "https://raw.githubusercontent.com/SASJedi/sas-macros/master/fileattribs.sas"; %include getmacro; filename getmacro url "https://raw.githubusercontent.com/SASJedi/sas-macros/master/translate.sas"; %include getmacro; filename getmacro url "https://raw.githubusercontent.com/SASJedi/sas-macros/master/findfiles.sas"; %include getmacro; /* Get syntax help in the log */ %FindFiles(?) /* List files in the s:/workshop and all sub-folders, save as a SAS data set */ %FindFiles(s:/workshop,,work.myFiles)

SASJedi · ‎07-29-2021

Fortunately, SAS has a function made just for this 😁 YRDIF. The syntax is (oldestDate,newestDate). You can express your newest date as a date constant like this: '01NOV2020'd. So here's some code that creates your starter data: data have; input Name:$10. DateOfBirth:yymmdd10.; format DateOfBirth yymmdd10.; datalines; Robert 1987-12-27 Alex 1946-11-23 Paul 1996-3-12 ; proc print data=have; run; Result: Obs Name DateOfBirth 1 Robert 1987-12-27 2 Alex 1946-11-23 3 Paul 1996-03-12 Next we process the data: data want; set have; AgeWithFractions=yrdif(DateOfBirth,'01Nov2020'd); Age=int(AgeWithFractions); run; proc print data=want; run; Result: Obs Name DateOfBirth AgeWithFractions Age 1 Robert 1987-12-27 32.8466 32 2 Alex 1946-11-23 73.9397 73 3 Paul 1996-03-12 24.6411 24 So let's be kind, and use the age without fractions as the result. We can calculate that straight up in a single expression like this: data want; set have; Age=int(yrdif(DateOfBirth,'01Nov2020'd)); run; proc print data=want; run; Result: Obs Name DateOfBirth Age 1 Robert 1987-12-27 32 2 Alex 1946-11-23 73 3 Paul 1996-03-12 24 May the SAS be with you! Mark

SASJedi · ‎07-26-2021

Moved to the Statistical Procedures forum, where it should get better exposure to folks with expertise in this matter.

SASJedi · ‎07-23-2021

See Encoding Compatibility between SAS/CONNECT Client and Server Sessions "To successfully use SAS/CONNECT programming services, the encodings of the client and server sessions must be compatible.... If one session is using UTF-8 and the other session has an unknown, or unsupported, encoding, an error occurs and the connection is not made." It appears that your SAS session encoding (Korean euc-kr) is not compatible with the server encoding (UTF-8). If you have the ability to start your local SAS session using UTF-8 encoding, you should try that.

SASJedi · ‎07-22-2021

I made some fake data like this: data union_combine; length id 8 ss_other_specify $255. Vaccination_Date1 $10 Manufacturer1 $350. Manufacturer2 $350. Vaccination_Code_Id1 $350. Vaccination_Code_Id2 $350.; retain Manufacturer1 "Big Pharma 1" Manufacturer2 "Big Pharma 2"; do id=1 to 4; Vaccination_Code_Id1 =choosec(ID,"No vaccine administered", "Other", "Unknown vaccine or immune globulin"," "); Vaccination_Code_Id2 =choosec(abs(5-ID),"No vaccine administered", "Other", "Unknown vaccine or immune globulin"," "); Vaccination_Date1='----------'; Vaccination_Date2='----------'; substr(Vaccination_Date1,6,2)='07'; substr(Vaccination_Date1,9,2)=put(ID,z2.); substr(Vaccination_Date1,1,4)='2021'; substr(Vaccination_Date2,6,2)='07'; substr(Vaccination_Date2,9,2)=put(ID+14,z2.); substr(Vaccination_Date2,1,4)='2021'; Manufacturer1 = choosec(ID,"JOHNSON AND JOHNSON","PFIZER","JOHNSON AND JOHNSON","PFIZER"); Manufacturer2 = choosec(ID,"PFIZER","JOHNSON AND JOHNSON","PFIZER","JOHNSON AND JOHNSON"); output; end; run; Then I ran this single data step that did all the conversions you were doing: data new_union; length ss_other_specify $255. Manufacturer1 $50. Manufacturer2 $50. Vaccination_Code_Id1 $50. Vaccination_Code_Id2 $50.; set union_combine (rename=(Vaccination_Date1 = _dt1 Vaccination_Date2 = _dt2)); /*Vax code format*/ if Vaccination_Code_Id1 in ("No vaccine administered", "Other", "Unknown vaccine or immune globulin") then Vaccination_Code_Id1 = ""; if Vaccination_Code_Id2 in ("No vaccine administered", "Other", "Unknown vaccine or immune globulin") then Vaccination_Code_Id2 = ""; /*Vax date format*/ Vaccination_Date1= mdy(substr(_dt1,6,2), substr(_dt1,9,2), substr(_dt1,1,4)); Vaccination_Date2= mdy(substr(_dt2,6,2), substr(_dt2,9,2), substr(_dt2,1,4)); Manufacturer1 = upcase(Manufacturer1); Manufacturer2 = upcase(Manufacturer2); if Manufacturer1 = "JOHNSON AND JOHNSON" then Manufacturer1 = "JANSSEN (JOHNSON AND JOHNSON)"; if Manufacturer1 = "PFIZER" then Manufacturer1 = "PFIZER/BIONTECH"; if Manufacturer2 = "JOHNSON AND JOHNSON" then Manufacturer2 = "JANSSEN (JOHNSON AND JOHNSON)"; if Manufacturer2 = "PFIZER" then Manufacturer2 = "PFIZER/BIONTECH"; drop _:; format Vaccination_Date1 mmddyy10. Vaccination_Date2 mmddyy10.; run; All the variable sizes I changed to $50 worked just fine: proc contents data=new_union; run; Alphabetic List of Variables and Attributes # Variable Type Len Format 2 Manufacturer1 Char 50 3 Manufacturer2 Char 50 4 Vaccination_Code_Id1 Char 50 5 Vaccination_Code_Id2 Char 50 7 Vaccination_Date1 Num 8 MMDDYY10. 8 Vaccination_Date2 Num 8 MMDDYY10. 6 id Num 8 1 ss_other_specify Char 255 Can you share your results?

SASJedi · ‎07-22-2021

Assuming you have the requisite permissions to promote & delete tables, here is the trouble as I see it: After your DROPTABLE statement deletes the table named GL_PERIODS_inCAS, the CAS table you want to put in its place (GL_PERIODS) doesn't yet exist. This is what is causing the error message: ERROR: There is no session-scope table GL_PERIODS in caslib Public of Cloud Analytic Services. You need to create a session-scope table named GL_PERIODS in the Public caslib before you begin this process. Once you have the session-scoped table available in Public, you can more easily accomplish your goal using CASL code (instead of PROC CASUTIL). Something like this should work: proc cas; table.dropTable / caslib="public" ,name="GL_PERIODS_inCAS" ,quiet=TRUE ; /* For Viya 3.4 and 3.5, use table.partition to copy the table */ table.partition / table={caslib="public",name="GL_PERIODS"} ,casout={caslib="public",name="GL_PERIODS_inCAS",promote=TRUE} ; /* For Viya 2020.1 and beyond, use table.copyTable */ table.copyTable / table={caslib="public",name="GL_PERIODS"} ,casout={caslib="public",name="GL_PERIODS_inCAS",promote=TRUE} ; quit; After verifying that all went well, you can drop the session-scoped table, or just disconnect from CAS. May the SAS be with you! Mark

SASJedi · ‎07-22-2021

If you are using a LIBNAME to access your DBMS table and loading the data to CAS with code like this, you are performing a client-side load: libname db sqlsrv <connect info>; proc casutil; load data=db.mytable casout="myCASTable" outcaslib="casuser"; run; As you can see, in a client-side load the data is first processed by the SAS/ACCESS LIBNAME engine, and so your large VARCHAR column is truncated before you get a chance to load it. If your CAS server has the associated data connector configured for your DBMS, you can do a server-side load instead. The code would look more like this: caslib sqlDB desc='Microsoft SQL Server Caslib' dataSource=(srctype='sqlserver' username='user1' password='myPwd' sqlserver_dsn="configured-DSN-name" catalog='*'); proc casutil; load casdata="myTable" incaslib="sqlDB" casout="myCASTable" outcaslib="casuser"; run; This loads the table directly from SQL Server to CAS using the CAS data connector: As you can see here, the LIBANME engine is not involved in this process, so the data is not converted to SAS numeric / fixed-width character before loading into CAS. So, are you doing a client-side load or a server-side load from SQL Server?

Online Status	Offline
Date Last Visited	Saturday