topic Re: Proc Append problem when BASE has never been initialize in SAS Programming

Proc Append problem when BASE has never been initialize

imdickson — Mon, 24 Sep 2018 07:22:30 GMT

Hi, I am trying to append a lot of datasets in a recursive macro.

However, when i set the BASE as dataset that has not been initialize, it will not produce any result.

%macro list_files(dir,ext);					
	%local filrf rc did memcnt name i;				
	%let rc=%sysfunc(filename(filrf,&dir));				
	%let did=%sysfunc(dopen(&filrf));				
					
	%if &did eq 0 %then				
		%do;			
			%put Directory &dir cannot be open or does not exist;		
					
			%return;		
		%end;			
					
	%do i = 1 %to %sysfunc(dnum(&did));				
		%let name=%qsysfunc(dread(&did,&i));			
					
		%if %qupcase(%qscan(&name,-1,.)) = %upcase(&ext) %then			
			%do;		
				%put &dir/&name;	
				%let file_name =  %qscan(&name,1,.);	
				%put &file_name;	
				/*Alfred added this*/	
			PROC IMPORT OUT=WORK.out DATAFILE= "&dir/&name" 		
		/*excelout*/			
            DBMS=csv REPLACE;					
			delimiter='09'x;		
			getnames=no;		
		RUN;			
		proc contents data=out noprint out=data_info /*(keep = name varnum)*/;			
		run;			
					
		 data _null_;			
			set data_info;		
			call symputx(compress("col"||VARNUM),compress(NAME));		
			call symputx("cnt",_n_);		
		run;			
					
					
					
					
		
					
		data _null_;			
		set WORK.out (obs=2);			
			if _n_ = 2 then do;		
			tcnt = 0;		
				%do j=1 %to &cnt;	
					if &&col&j not in ("","Total") then do;
					trxm = &&col&j;
					call symputx(compress("trxm"||tcnt),compress(trxm));
					call symputx("tcnt",tcnt);
					tcnt+1;
					end;
				%end;	
			end;		
		run;			
		 			
	
		
					
		
					
					
					
		data test (drop=			
		%do k=1 %to &cnt;			
		&&col&k..			
		%end;			
		);			
					
		length station $10 voltage $10 year 8 month $20 transformer $10			
		Day $20 Date Time MW_Imp MW_Exp MVAR_Imp MVAR_Exp MVA Power_Factor 8;			
		format Time hhmm.;			
		set 			
		out end=last;			
					
		output;			
		%end;			
		end;			
		run;			
	
							
		proc append base=finaltest data=test FORCE;			
		run;			
				
		data finaltest;
		run;

Sample code as above.

These are not the full code. What i want to do is append data everytime it loops. I do not want to assign the loop counter for every dataset.

Moreover, is it necessary to write a dataset and define column names for "finaltest" the BASE table first?

I read somewhere that BASE table doesnt have to initialize first.

Correct me if i am wrong.

Re: Proc Append problem when BASE has never been initialize

RW9 — Mon, 24 Sep 2018 07:39:38 GMT

Not going to answer the question as this has been talked about time and time again. Use the inbuilt SAS function called by group processing rather than create many same datasets and then appending. This will save you disk space, read/writes, enable you to write short concise coding. None of the code you presented is necessary, you have a bunch of csv files, you need to import, therefore write one datastep import - key to gettting data correctly in (import guesses), then use a wildcard in the infile to read all the files, very simple easy coding:

data want;
  infile "pathtofiles/*.csv" dlm=",";
  length...;
  format...;
  informat...;
  input...;
run;

Dont actually even need to use by group processing here.

Re: Proc Append problem when BASE has never been initialize

s_lassen — Mon, 24 Sep 2018 08:16:27 GMT

Obviously, your "finaltest" data set will always be empty when you run this code, because of the last couple of statements:

data finaltest;
run;

Re: Proc Append problem when BASE has never been initialize

Reeza — Mon, 24 Sep 2018 15:00:06 GMT

However, when i set the BASE as dataset that has not been initialize, it will not produce any result.

That is wrong, PROC APPEND does not require the base data set to exist before creation.

proc append base=testR12345 data=sashelp.class;run;

Log:

1 proc append base=testR12345 data=sashelp.class;run;
NOTE: Writing HTML Body file: sashtml.htm

NOTE: Appending SASHELP.CLASS to WORK.TESTR12345.
NOTE: BASE data set does not exist. DATA file is being copied to BASE file.
NOTE: There were 19 observations read from the data set SASHELP.CLASS.
NOTE: The data set WORK.TESTR12345 has 19 observations and 5 variables.
NOTE: PROCEDURE APPEND used (Total process time):
real time 3.95 seconds
cpu time 0.28 seconds

Re: Proc Append problem when BASE has never been initialize

imdickson — Mon, 24 Sep 2018 21:35:51 GMT

Hi Reeza, thanks for the clarification. If i do not initialize the base table, how do i output it out? I am doing it in EG but not want to use proc sql.