tags: SAS || | CATS() MERGE Windows10 Azure

Debugging SAS programs

Access SAS Studio on Azure

Problem In Windows10, Start> Photo icon > Change account settings > Access work or school > Connected to GREENLIGHT CLINICAL PTY LTD's Azure AD > Info > Sync.

Device sync status is always "The sync is in progress"

or The sync can not be initiated (0x1)

---

Server connection and profile

ERROR An error occurred executing the workspace job "autoexec". SDS Failed to provide the SAS workspace. SAS.EC.Directory.Model.SDSEXception

Cause The cause is unclear.

Solution Closing and relaunching SAS EG resolved the error

---

ERROR A local SAS server was not found and no metadata profile is being used. Without a SAS server you cannot open data or run SAS programs

Cause Credentials lost to user profile. SAS EG is not able to connect to server

Solution Tools> Connections> Profiles> Modify >

• Uncheck "Use Integrated Windsows Authentication"

• Check "Save login in profile"

• Enter User, Password and Authenticaion domain as the following
  

• Save

• Click on the profile > Set Active

• On the buttom right corner of SAS EG window, it should show "SAS" rather than "No SAS Profile"

---

Formatting

ERROR Formatted values shown as dot in character variable but there is no problem with the format applied
Cause Wrong use of the iterator as highlighted below

SAS log

Each place is given by: (Number of times) at (Line):(Column). 2648 at 37:13 Line is the line number, 37 here. 13 is number of characters, highlighted below, between line 13 and the code

---

ERROR A lock is not available for QSDTM.QC_PE.DATA. The SAS dataset to overwrite is not used by another user or process as

And the SAS dataset cannot be deleted

---

ERROR ERROR: File SPECS.DATASETS.DATA does not exist.

Cause The file is opened by another user

---

ERROR NOTE: Character values have been converted to numeric values at the places given by: (Line):(Column).

Cause Concatenate operator || misused as the pipe |

---

Error An error occurred executing the workspace job The server is disconnected

Cause Slow connection to AWS. This disconnection cancels out the current SAS session. Bring back the macro variables, rerunning autoexec.sas, before rerun the current SAS program

---

Error: NOTE: Mathematical operations could not be performed at the following places. The results of the operations have been set to missing values. Each place is given by: (Number of times) at (Line):(Column). 15 at 91:30 15 at 92:27

Cause CATS() function placed inside INPUT() function (code commented out below) not working

	else if yn_complete_date_AEENDTC="no" then do;
		AEENDTC_date_min_c=prxchange('s/-NK/-01/',-1,AEENDTC);
		AEENDTC_date_min_n=input(AEENDTC_date_min_c,yymmdd10.);
		AEENDTC_date_max_c= put(INTNX('month', AEENDTC_date_min_n, 0,'e'),yymmdd10.);
		AEENDTC_date_max_n= input(AEENDTC_date_max_c, yymmdd10.);
/*		AEENDTC_datetime_min_n= input(CATS(AEENDTC_date_min_c,"T00:00"),B8601DT.);*/
		AEENDTC_datetime_min_n= input((AEENDTC_date_min_c||"T00:00"),B8601DT.);
/*		AEENDTC_datetime_max_n= input(CATS(AEENDTC_date_max_c,"T23:59"),B8601DT.);*/
		AEENDTC_datetime_max_n= input((AEENDTC_date_max_c||"T23:59"),B8601DT.);
	end;

Cause: Variables AESTDTC_date_min_c and AESTDTC_date_max_c defined in ELSE statement but they are not defined in IF statement

/*Create earliest possible, latest possible date and datetime for AESTDTC*/
if yn_complete_date_AESTDTC="yes" then do;
	AESTDTC_date_min_n=input(AESTDTC, yymmdd10.); 
	AESTDTC_date_max_n=input(AESTDTC, yymmdd10.);
	AESTDTC_datetime_min_n=input(CATS(AESTDTC,"T00:00"),B8601DT.);
	AESTDTC_datetime_max_n=input(CATS(AESTDTC,"T23:59"),B8601DT.);
end;
else if yn_complete_date_AESTDTC="no" then do;
	AESTDTC_date_min_c=prxchange('s/-NK/-01/',-1,AESTDTC);
	AESTDTC_date_min_n=input(AESTDTC_date_min_c,yymmdd10.);
	AESTDTC_date_max_c= put(INTNX('month', AESTDTC_date_min_n, 0,'e'),yymmdd10.);
	AESTDTC_date_max_n= input(​AESTDTC_date_max_c, yymmdd10.);
	AESTDTC_datetime_min_n= input(CATS(AESTDTC_date_min_c,"T00:00"),B8601DT.);
	AESTDTC_datetime_max_n= input(CATS(​AESTDTC_date_max_c,"T23:59"),B8601DT.);
end;

---

ERROR NOTE: Merge statement has more than one data set with repeats of BY values

Cause If you merge two files by some key variable and the resulting dataset has more observations that the largest input file then it means that there are key values that are present in only one of the files. So if you merge A and B by ID where A has 10 obs and B has 8 obs and the result has more than 10 obs then it there must be some values of ID that appear on only one of the two inputs…MERGE statement has more than one data set with repeats of BY values

Note in the folloing code the resulting dataset has the same obs number as the left table but the warning is still there

data work.DS_DM;
	length DSEPRELI $2 DSEPRELI_temp $3;
	merge work.DS_meta(in=T1) 
		  work.SDTM_DM(drop=USUBJID rename=(SUBJID=SubjectId) in=T2)
		  work.EOT(keep=SubjectId EOTCOV19 in=T3)
		  work.EOS(keep=SubjectId EOSCOV19 in=T4)	  		
	;
	by SubjectId;
	if T1;
/*Specify patterns for complete date and time using regular expressions*/
	pattern_date=prxparse("/\d{4}-\d{2}-\d{2}/");
/*Check date completeness of DSSTDTC
Get datepart as numeric*/	
	if prxmatch(pattern_date, DSSTDTC) not=0 then do;
		yn_complete_DSSTDTC="yes";
		DSSTDTC_datepart_n=datepart(input(DSSTDTC,B8601DT.));
	end;
	else do; 
		yn_complete_DSSTDTC="no"; 
		DSSTDTC_datepart_n=.;
	end;
/*Create DSSTDY
Number of days before or after RFSTDTC derived as follows: 
if --STDTC is on or after DM.RFSTDTC, --STDY = (date portion of --STDTC) - (date portion of DM.RFSTDTC) + 1;  
if --STDTC precedes DM.RFSTDTC, --STDY = (date portion of --STDTC) - (date portion of DM.RFSTDTC).*/
	if DSSTDTC_datepart_n not=. and RFSTDTC_datepart_n not=. then do;
		if DSSTDTC_datepart_n >= RFSTDTC_datepart_n then do;
			DSSTDY= DSSTDTC_datepart_n - RFSTDTC_datepart_n + 1;
		end;
		else if DSSTDTC_datepart_n < RFSTDTC_datepart_n then do;
			DSSTDY= DSSTDTC_datepart_n - RFSTDTC_datepart_n;
		end;	
	end;
/*Create DSEPRELI
CRF.EOT.EOTCOV19 where DSSCAT = "STUDY TREATMENT"; 
CRF.EOS.EOSCOV19 where DSSCAT = "STUDY PARTICIPATION". In both cases, "Yes" to "Y", "No" to <null>*/
	if DSSCAT = "STUDY TREATMENT" then DSEPRELI_temp=EOTCOV19;
	else if DSSCAT = "STUDY PARTICIPATION" then DSEPRELI_temp=EOSCOV19;

	if DSEPRELI_temp="No" then DSEPRELI="";
	else if DSEPRELI_temp="Yes" then DSEPRELI="Y";
	else DSEPRELI="";
	drop DSEPRELI_temp;

	format DSSTDTC_datepart_n yymmdd10.;
run;
/*NOTE: MERGE statement has more than one data set with repeats of BY values.*/
/*NOTE: There were 876 observations read from the data set WORK.DS_META.*/
/*NOTE: There were 175 observations read from the data set WORK.SDTM_DM.*/
/*NOTE: There were 125 observations read from the data set WORK.EOT.*/
/*NOTE: There were 114 observations read from the data set WORK.EOS.*/
/*NOTE: The data set WORK.DS_DM has 876 observations and 27 variables.*/