[prev in list] [next in list] [prev in thread] [next in thread] 

List:       sas-l
Subject:    Re: A MACRO/SCAN FUNCTION QUESTION - thank you (urgent)
From:       "Martin, Vincent (STATCAN)" <vincent.martin () CANADA ! CA>
Date:       2017-02-24 21:16:50
Message-ID: SAS-L%201702241616520668.7F1C () LISTSERV ! UGA ! EDU
[Download RAW message or body]


Responding to the follow-up question:
	Oh boy! say about I have 20 varaibles from each datasets. Now I want to get rid of 2 \
particular variables names "Dod" and "sod" from 	that scan function. Like I want to \
skip scanning this two variables, How do I do it in this given code.

I just needed the original question to keep track. Since the scan function runs on \
datasets and not variables, it is unclear whether you wish to remove dod/sod from the \
datasets (i.e. drop the variables) or remove datasets named dod/sod from your DSLIST \
macvar. Furthermore, if the variables are not present on absolutely all datasets in \
the list, the answer is more complex.

If the variables are part of EACH dataset in your programmatically generated DSLIST \
macvar, you can simply use dataset options, erther in the macro code loop or directly \
in the proc sql with ... select cats(memname, "(drop=dod sod)") into... or something \
along these lines. I think Nat sent you the code where the DS option was used in the \
macro loop.

If you mean that dod and sod are table names that shouldn't be read, you can simply \
add a condition in your sql WHERE clause to exclude them.

If only a subset of the tables in DSLIST have variables dod and/or sod, then you \
would need a much more complex query joining vtable and vcolumn. Something like \
(untested - shows the basic gist though)

Select (case when dod_flag=0 and sod_flag=0 then memname
		When dod_flag=1 and sod_flag=0 then cats(memname, "(drop=dod)")
		When dod_flag=0 and sod_flag=1 then cats(memname, "(drop=sod)")
		Otherwise cats(memname, "(drop=dod sod)")
	End)
	Into :dslist separated by ''

	From (select a.memname, b.dod_flag, b.sod_flag

		From (select memname
			from sashelp.vstable
			where trim(left(upcase(libname))) eq "RAW" and 
			  substr(upcase(memname),1,5) ne "ORIG_";) a,
			,
			(select memname,
			 max(name="SOD") as sod_flag, 
			max(name="DOD") as dod_flag
			 from sashelp.vcolumn
			 group by memname)  b

		Where a.memname=b.memname
		)
	;


hth


-----Original Message-----
From: SAS(r) Discussion [mailto:SAS-L@LISTSERV.UGA.EDU] On Behalf Of Tom Smith
Sent: February-24-17 12:44 PM
To: SAS-L@LISTSERV.UGA.EDU
Subject: A MACRO/SCAN FUNCTION QUESTION - thank you (urgent)

THIS IS THE GIVEN MACRO:

%MACRO Washington;

	proc sql noprint; 
		select memname into:dslist separated by  ' ' 
		from sashelp.vstable
		where trim(left(upcase(libname))) eq "RAW" and 
			  substr(upcase(memname),1,5) ne "ORIG_"; 
	quit; 

	%put &dslist; 

	%let i = 1; 

	%do %while (%length(%scan(&dslist,&i,%str( ))));

		%let ds = %scan(&dslist,&i,%str( )); 
		
		data ORIG.orig_&ds adj.&ds; 
			set raw.&ds; 
		run; 

		%let i = %eval(&i+1); 

	%end; 

%MEND Washington;




This Macro is stopping to read datasets for once it reaches special characters such \
as "->" and giving the below log:




ERROR: Some character data was lost during transcoding in the dataset MAIN.POCO. \
                Either the data
       contains characters that are not representable in the new encoding or \
truncation occurred  during transcoding.
NOTE: The DATA step has been abnormally terminated.
NOTE: The SAS System stopped processing this step because of errors.
NOTE: There were 29 observations read from the data set MAIN.POCO.
WARNING: The data set SUB.SUB_POCO may be incomplete.  When this step was stopped \
there were 29  observations and 24 variables.
WARNING: The data set REVIEW.POCO may be incomplete.  When this step was stopped \
there were 29  observations and 24 variables.



WHAT CHANGES I CAN MADE TO THE GIVEN CODE/MACRO SO THAT SPECIAL CHARACTER WON'T STOP \
SAS FROM READING DATASETS OR GIVING ANY ERROR?

Thank you so much from the Bottom of my heart.


[prev in list] [next in list] [prev in thread] [next in thread]