topic Re: Marco questions in SAS Programming

Marco questions

Gil_ — Thu, 02 Feb 2017 17:33:50 GMT

I have a table named Raw
That looks like this

Create date Tracking number AtM1
01/25/17. 1ZT248. Bagbar:"RS7779704"

Atm2. Atm3
ATMBAR:"IN6618220125. Bagbar:"RS7779703"

ATM4. ATM5
ATMBAR: "IN540322025" BAGBAR:"RS7779167"
txt me? 352-440-3968

ATM8
ATMBAR:"IN1933220125"

these are in one row horizontal the macro looks like this

Proc sql no print;
Create table numatms as select
Distinct name,input (substr (name,4,4),8.0) as num
From sashelp.vcolumn where libname='work' and memname='RAW' and substr (name,1,3)="ATM"
ORDER BY 2;
QUIT;
PROC SQL NO print ; select max (num) into :nuatms from numatms;quit;
%put &numatms;
Proc sort data =raws; by Createdate trackingnumber; run;
Data outputs_results;run;

%Marco c (i);
%let j=%sysfunc (sum (&i,1));
%put &j.;

Data ext ( rename=(atm&i=atm_bag atm&j=atm_bar));
Format Createdate date9. Trackingnumber $18.;
Length atmi. Atmj. $256;
Set raw (keep=Createdate trackingnumber atm&i. Atm&j. );
Run;

Data outputs_results;
Set outputs_results ext;
Label atm_bag ="ATM" ATM_BAR="ATM";
IF CREATEDATE =. then delete;
Run;
%mend;
%marco r;
%do k=1 %to &numatms;
%if %sysfunc (mod (&k.,4))=1%then %do;
%c (&k);
%end;
%end;
%mend;
%r

Output
Is only 2 out of 4
Barcode RS7779704
BARCODE RS7779167

I don't know why it excludes the other two ... there is no spaces or character

Thanks

Re: Marco questions

Gil_ — Thu, 02 Feb 2017 17:34:14 GMT

Re: Marco questions

ballardw — Thu, 02 Feb 2017 17:55:57 GMT

It is very hard to follow what your data is, the result and what you expected. It helps to post your data in the form of data step code so we can understand what you have or your result actually looked like. Instructions here: https://communities.sas.com/t5/SAS-Communities-Library/How-to-create-a-data-step-version-of-your-data-AKA-generate/ta-p/258712 show how to creat datastep code from a SAS data set. You should paste the code in a forum code box opened with the {i} icon.

One possbile contributor is this line in your Proc SQL code:

From sashelp.vcolumn where libname='work' and memname='RAW' and substr (name,1,3)="ATM"

The values for LIbname and memname are both stored in UPPERCASE only. So you may not be getting the results you expect. Secondarily the NAME is in mixed case so if the variable is 'atm2' then substr (name,1,3)="ATM" won't match. Use substr(upcase (name,1,3))="ATM" to fix that.

I am not sure what you are attempting but the code as written only keeps 2 atm variables if that is what you a re asking about:

Set raw (keep=Createdate trackingnumber atm&i. Atm&j. );

If you post some data and what the expected result for that example data is then we may be able to provide more help. A description of what you are actually attempting wouldn't hurt.

Re: Marco questions

Astounding — Thu, 02 Feb 2017 18:25:53 GMT

I can't take the time to thoroughly analyze this, but there is a prime suspect here:

%if %sysfunc (mod (&k.,4))=1%then %do;

This statement means that your interior macro executes for every 4th line only (k=1, k=5, k=9, k=13, etc.).

That logic may have been inserted for testing purposes, or perhaps to obtain a sample of the incoming data.

Re: Marco questions

Gil_ — Thu, 02 Feb 2017 19:54:12 GMT

Thsnks astounding I change the number to 7 and it brought in the 2 thst was missing. I will run a full set of data to see if it fixes it

Re: Marco questions

Gil_ — Fri, 03 Feb 2017 17:40:28 GMT

Re: Marco questions

Gil_ — Fri, 03 Feb 2017 17:50:54 GMT

I tried the uppercase same results the raw data looks like this
Date tracking ATM1 ATM2 ATM3 ATM4 ATM5 ATM6
The out put I would like to see
Date tracking ATM1 ATM2
Date tracking ATM3 ATM4
Date tracking ATM5 ATM6

Once I change the number%if %sysfunc (mod (&k.,4))=1%then %do; to 7 the col would overlap
it shows
ATM ATM