topic Re: How can add dynamically variables while importing a file? in SAS Data Management

How can add dynamically variables while importing a file?

Kal_ETL — Wed, 19 Dec 2018 16:24:31 GMT

Hello Community,

I have a couple of text files fixed width. The thing is that it is a business rule that after certain position, all variables should be created dynamically with a numeric consecutive sufix (1,2,3....n). I know n value and positions to read for each one. I did a do loop from 1 to n and works fine (tested in EG with dummy values).

But now I wanted to use my macro after the Filereader doesn't work, I am putting it into a user written code. I think I need to put while reading the files since will extract infor from files.

Does anyone has any tip on how to do this, or did something like this in the past?

Any example or tip would be great.

Regards.

Re: How can add dynamically variables while importing a file?

SuryaKiran — Wed, 19 Dec 2018 16:37:00 GMT

Hello,

Not sure exactly what your trying to do. Can you please post with some example code or data.

Input statemnt can be given as var1-var10 for variables var1,var2,var3,...........var10.

Re: How can add dynamically variables while importing a file?

Kal_ETL — Wed, 19 Dec 2018 17:36:50 GMT

Thank you.

Basically I have this

What i want to do is to add this macro (it works fine in sas base code), I tryed with a user written code after File Reader but not working

%macro ciclo;
%let pos1=285;
%let pos2=286;
%let pos3=289;
%let pos4=296;

%let finciclo=450;
	%do x=1 %to &end%by 1;
	    input
		@&pos1. IB_ACTION_FLAG_&x. $EBCDIC1.
		@&pos2. IB_INST_OCCUR_SEQ_&x. $EBCDIC3. @;
		if not missing(IB_INST_OCCUR_SEQ_&x.) then do;
		  input
		  @&pos3. IB_INST_AMT_&x. S370FPD7.2
		  @&pos4. IB_INST_PRIN_&x. S370FPD7.2;


	%end;
%put &pos1;
%put &pos2;
%put &pos3;

%mend;


data temp2;

%ciclo;

run;

Not sure how can I translate this into DI, any advice?

Names and number positions are dummy for confidentiallity.

Re: How can add dynamically variables while importing a file?

Reeza — Wed, 19 Dec 2018 17:55:16 GMT

I don't see an infile statement in that code. How are you linking it to the data source?

Re: How can add dynamically variables while importing a file?

Kal_ETL — Wed, 19 Dec 2018 22:49:59 GMT

You're right.

However I think I solve part of the problem. I put the code into the FileReader using the code generation mode: user writting code. The I added my macro and called it below last field from the input. Something like this

data work.W665GGA2 / view = work.W665GGA2 ;
   infile "/SASDATA/staging/filename.TXT"
          lrecl = 16464
          truncover
          firstobs = 1
          recfm=f;
   ;

input @ 1 field1 $EBCDIC19.
@ 20 field2 S370FPD4.
@ 24 field3 S370FPD4.

%macrocall that add variables dynamically;

It worked fine, but now I don't know why it is loading only the half, since log says:

NOTE: There were 45800 observations read from the data set WORK.W665W02N.
NOTE: 45800 observations added.
NOTE: The data set STG_TE_NB_M has 45800 observations and 3197 variables.
NOTE: 91600 records were read from the infile "/SASDATA/staging/filename.TXT".

Re: How can add dynamically variables while importing a file?

Reeza — Wed, 19 Dec 2018 22:57:42 GMT

Are you certain on your LRECL setting?

Re: How can add dynamically variables while importing a file?

Kal_ETL — Thu, 20 Dec 2018 05:08:24 GMT

Hello Reeza,

No I'm not. Those are files from Mainframe. How can i know the correct lrecl? I was trying differente but always load the half of the read from file.

Re: How can add dynamically variables while importing a file?

Kurt_Bremser — Thu, 20 Dec 2018 09:25:03 GMT

@Kal_ETL wrote:

%macro ciclo;
%let pos1=285;
%let pos2=286;
%let pos3=289;
%let pos4=296;

%let finciclo=450;
	%do x=1 %to &end%by 1;
	    input
		@&pos1. IB_ACTION_FLAG_&x. $EBCDIC1.
		@&pos2. IB_INST_OCCUR_SEQ_&x. $EBCDIC3. @;
		if not missing(IB_INST_OCCUR_SEQ_&x.) then do;
		  input
		  @&pos3. IB_INST_AMT_&x. S370FPD7.2
		  @&pos4. IB_INST_PRIN_&x. S370FPD7.2;


	%end;
%put &pos1;
%put &pos2;
%put &pos3;

%mend;


data temp2;

%ciclo;

run;

You use &end, which is nowhere defined.

You do not change the positions within the %do loop, so you will always read the same content.

The second input statement in the %do loop does not have a line hold (@), so the next input will read from the next line in the infile.

If &end were 2, you would therefore get only half the expected observations.

Re: How can add dynamically variables while importing a file?

Kurt_Bremser — Thu, 20 Dec 2018 09:44:36 GMT

Note that reading a certain structure multiple times can be done without macro processing in a data step do loop:

data test;
array x1{8} $1;
array x2{8} 8;
do i = 1 to 8;
  input
    @((i-1)*2+1) x1{i} $1.
    @((i-1)*2+2) x2{i} 1.
    @
  ;
end;
cards;
A1B2C3D4E5F6G7H8
;
run;

Re: How can add dynamically variables while importing a file?

Kal_ETL — Thu, 20 Dec 2018 19:43:13 GMT

You are right, but I don't know why it is bad copied. &end should be the macrovariable before that line: finciclo, declared to 450.

This is complete and correct macro, honestly this code was previously developed and nobody knows exactly the business rule in order to rebuild or optimize it.

%macro ciclo;
%let pos1=265;
%let pos2=266;
%let pos3=269;
%let pos4=276;
%let pos5=283;
%let pos6=290;
%let pos7=297;

%let finciclo=450;
	%do x=1 %to &finciclo %by 1;
	    input
		@&pos1. NTIB_IB_ACTION_FLAG_&x. $EBCDIC1.
		@&pos2. NTIB_IB_INST_OCCUR_SEQ_&x. $EBCDIC3. @;
		if not missing(NTIB_IB_INST_OCCUR_SEQ_&x.) then do;
		  input
		  @&pos3. NTIB_IB_INST_AMT_&x. S370FPD7.2
		  @&pos4. NTIB_IB_INST_PRIN_&x. S370FPD7.2
		  @&pos5. NTIB_IB_INST_INT_&x. S370FPD7.2
		  @&pos6. NTIB_IB_INST_FEE_&x. S370FPD7.2
		  @&pos7. NTIB_IB_INST_RELEASE_DATN_&x. S370FPD4.0 @;

         %CNVDATE(NTIB_IB_INST_RELEASE_DATN_&x. , julndt,sdtout=NTIB_IB_INST_RELEASE_DATE_&x. );

         %if &x=2 %then ;;

	     %let pos1=&pos1+36;
         %let pos2=&pos2+36;
         %let pos3=&pos3+36;
         %let pos4=&pos4+36;
         %let pos5=&pos5+36;
         %let pos6=&pos6+36;	
         %let pos7=&pos7+36;
	%end;
	%do x=1 %to &finciclo %by 1;
			end;
	%end;
%put &pos1;
%put &pos2;
%put &pos3;

%mend;

Re: How can add dynamically variables while importing a file?

Kal_ETL — Thu, 20 Dec 2018 21:19:32 GMT

Good advice, thank you for sharing this. It works great!

Re: How can add dynamically variables while importing a file?

Kurt_Bremser — Fri, 21 Dec 2018 08:05:34 GMT

@Kal_ETL wrote:

%macro ciclo;
%let pos1=265;
%let pos2=266;
%let pos3=269;
%let pos4=276;
%let pos5=283;
%let pos6=290;
%let pos7=297;

%let finciclo=450;
	%do x=1 %to &finciclo %by 1;
	    input
		@&pos1. NTIB_IB_ACTION_FLAG_&x. $EBCDIC1.
		@&pos2. NTIB_IB_INST_OCCUR_SEQ_&x. $EBCDIC3. @;
		if not missing(NTIB_IB_INST_OCCUR_SEQ_&x.) then do;
		  input
		  @&pos3. NTIB_IB_INST_AMT_&x. S370FPD7.2
		  @&pos4. NTIB_IB_INST_PRIN_&x. S370FPD7.2
		  @&pos5. NTIB_IB_INST_INT_&x. S370FPD7.2
		  @&pos6. NTIB_IB_INST_FEE_&x. S370FPD7.2
		  @&pos7. NTIB_IB_INST_RELEASE_DATN_&x. S370FPD4.0 @;

         %CNVDATE(NTIB_IB_INST_RELEASE_DATN_&x. , julndt,sdtout=NTIB_IB_INST_RELEASE_DATE_&x. );

         %if &x=2 %then ;;

	     %let pos1=&pos1+36;
         %let pos2=&pos2+36;
         %let pos3=&pos3+36;
         %let pos4=&pos4+36;
         %let pos5=&pos5+36;
         %let pos6=&pos6+36;	
         %let pos7=&pos7+36;
	%end;
	%do x=1 %to &finciclo %by 1;
			end;
	%end;
%put &pos1;
%put &pos2;
%put &pos3;

%mend;

What will this macro do?

What first hits my eye are staments like this:

%let pos1=&pos1+36;

The macro preprocessor does not do calculations on its own (with exceptions in %if statements), so &pos1 will end up as

265+36+36+36+(in the end, 450 times "+36");

The preprocessor is just a code generator, nothing more!

Much better would be

%let pos1=%eval(&pos1+36);

which forces an immediate calculation.

Then I played "macro processor" for you, and "ran" the core of the macro for 2 iterations. The resulting code would look like this (with some formatiing done by me to reveal the structure):

input
  @265 NTIB_IB_ACTION_FLAG_1 $EBCDIC1.
  @266 NTIB_IB_INST_OCCUR_SEQ_1 $EBCDIC3. @
;
if not missing(NTIB_IB_INST_OCCUR_SEQ_1)
then do;
  input
    @269 NTIB_IB_INST_AMT_1 S370FPD7.2
    @276 NTIB_IB_INST_PRIN_1 S370FPD7.2
    @283 NTIB_IB_INST_INT_1 S370FPD7.2
    @290 NTIB_IB_INST_FEE_1 S370FPD7.2
    @297 NTIB_IB_INST_RELEASE_DATN_1 S370FPD4.0 @
  ;
  input
    @301 NTIB_IB_ACTION_FLAG_2 $EBCDIC1.
    @302 NTIB_IB_INST_OCCUR_SEQ_2 $EBCDIC3. @
  ;
  if not missing(NTIB_IB_INST_OCCUR_SEQ_2)
  then do;
    input
      @305 NTIB_IB_INST_AMT_2 S370FPD7.2
      @312 NTIB_IB_INST_PRIN_2 S370FPD7.2
      @319 NTIB_IB_INST_INT_2 S370FPD7.2
      @326 NTIB_IB_INST_FEE_2 S370FPD7.2
      @333 NTIB_IB_INST_RELEASE_DATN_2 S370FPD4.0 @
    ;
  end;
end;

You can see that the whole input of the "2" variables is dependent on the if condition for the "1" variables. This will of course perpetuate for all variable groups from 2 to 450 in your original macro. The first time NTIB_IB_INST_OCCUR_SEQ_x is missing, the whole input processing is terminated. Is this what you want?

If this is actually done deliberately, because you could have only missing values from a certain point on, I would also STRONGLY recommend to not store all these variables in one observation, but create a vertical dataset with one observation per variable group. That way you don't waste space for all those missing values.

My original suggestion would then change to

data test;
do i = 1 to 8;
  input
    x1 $1.
    x2 1.
    @
  ;
  if not missing(x2) then output;
end;
cards;
A1B2C3D4E5F6G7H8
A1B2C3
;
run;

See how the code is much simpler?

Re: How can add dynamically variables while importing a file?

AnnaBrown — Fri, 21 Dec 2018 14:15:51 GMT

I'm glad you found some useful info, @Kal_ETL! If one of the replies was the exact solution to your problem, can you "Accept it as a solution"? Or if one was particularly helpful, feel free to "Like" it. This will help other community members who may run into the same issue know what worked.

Thanks!
Anna