DATA Step, Macro, Functions and more

Do Loop in macro not replacing the column name

Reply
New Contributor
Posts: 3

Do Loop in macro not replacing the column name

Hi ,

I have 12 files each with same number of columns. I need to merge them but there is a problem after I import because there are two columns related to rank which are both numeric and char.

Say file 1 is snout2 which has rank01 and rank02

file 2 is snout3 which has rank02 and rank03

file 3 is snout4 having column rank03 and rank04

All of these files have these rank columns either as Char or Num.

I need to cast these columns in each file as char 3 in an iterative manner as i have many files.

i used the below code and it is not working

%MACRO CHGFMT (i=);

%DO i=1 %TO 13;

j=i-1;

DATA SNOUT&i_CHAR;

set SNOUT&i;

RANK&i_Ch = strip(put(rank&i,3.));

RANK&j_Ch = strip(put(rank&j,3.));

DROP rank&i;

DROP rank&j;

RENAME rank&i =RANK&i_CH;

RENAME rank&j =RANK&j_CH;

RUN;

PROC PRINT DATA=SNOUT&i_CHAR;

RUN;

PROC CONTENTS DATA=SNOUT&i_CHAR;

RUN;

%END;

%MEND;

%CHGFMT;

i get syntax error after executing and the log doesn't give me a clear pic. Can anyone please help me

Thanks

Super Contributor
Super Contributor
Posts: 3,174

Re: Do Loop in macro not replacing the column name

It's clear you will get a WARNING message with the DROP and RENAME usage - remove the DROP reference as it does not apply -- you are doing the RENAME at step-termination.

As well, recommend self-initiated desk-checking with adding OPTIONS SOURCE SOURCE2 MACROGEN SYMBOLGEN MLOGIC MPRINT;    in order to generate the max amount of SAS log diagnostic output.  Then if needed, reply to the original post and COPY/PASTE the exact SAS diagnostic WARNING / ERROR messages generated.

Scott Barry

SBBWorks, Inc.   

Super User
Posts: 5,069

Re: Do Loop in macro not replacing the column name

You have a few issues that you will need to learn about.  I'll pose a couple of questions to get you started ... see what you can find for answers.

j = i - 1;

Is this supposed to create a macro variable &J, by performing math on the macro variable &I?  You'll need to find out how to do that.

&i_CHAR

How does SAS know that the name of the macro variable is &i?  Perhaps it should be &i_?  Or perhaps the macro variable is named &i_CH?  When using a long string that contains the name of a macro variable, you will need to know how to tell SAS what the name of the macro variable is.

Good luck.

Super User
Posts: 17,734

Re: Do Loop in macro not replacing the column name

SagayamaryPriti wrote:

I need to merge them but there is a problem after I import because there are two columns related to rank which are both numeric and char.

Fix your import instead, avoid this step entirely.

New Contributor
Posts: 3

Re: Do Loop in macro not replacing the column name

I did the import and had the do loop to do many files.I tried the put function within the import but it didn't work.I had posted the log and modified  to reply to Astounding. Still getting error

%MACRO IMPORT (i=);

%DO i=10 %TO 13;

PROC IMPORT OUT=SNOUT&i

datafile ="C:\Users\spj1000\Downloads\July 7-selected\HW Assignment PM\SN&i..csv"

DBMS=CSV REPLACE;

guessingrows=100;

GETNAMES=YES;/* sTART THE DATA FROM SECOND ROW AND FIRST ROW IS VARIABLE NAME iT PUTS _ FOR SPACE IN COL NAMES*/

DATAROW=2;

RUN;

/*DATA SN&i_OUT_CHAR;

SET SNOUT&i;

RANK&i_Ch = strip(put(rank&i,3.));

DROP rank&i;

RENAME rank&i =RANK&i_CH;

RUN;*/

PROC SORT DATA=SNOUT&i;

BY Brand;

RUN;

PROC CONTENTS DATA=SNOUT&i;

RUN;

%END;

Super User
Super User
Posts: 6,495

Re: Do Loop in macro not replacing the column name

So if you are using PROC IMPORT to read a CSV file then you are exposing yourself to this type of variable type mismatch.  That is because PROC IMPORT must try to guess what type of variables your CSV file has by looking at the data in the file.  Since each file has different data it can lead to different decisions. If your CSV files are well formed (or even if they aren't) you will have more control by reading them using data steps that you write rather than letting PROC IMPORT guess about what your data means.

Contributor ndp
Contributor
Posts: 61

Re: Do Loop in macro not replacing the column name

try this:

%MACRO IMPORT;

%DO i=1 %TO 13;

PROC IMPORT OUT=SNOUT&i

datafile ="C:\Users\spj1000\Downloads\July 7-selected\HW Assignment PM\SN&i..csv"

DBMS=CSV REPLACE;

guessingrows=100;

GETNAMES=YES;/* sTART THE DATA FROM SECOND ROW AND FIRST ROW IS VARIABLE NAME iT PUTS _ FOR SPACE IN COL NAMES*/

DATAROW=2;

RUN;


data _null_;

set sashelp.vcolumn end=eof;

WHERE LIBNAME="WORK" AND MEMNAME="SNOUT&i" AND upcase(NAME)=:"RANK" AND upcase(TYPE)="NUM"; *** select numeric rank vars;

var1=strip(name)||"_ch = strip(put("||strip(name)||",best.));";

var2="drop "||strip(name)||";";

var3="rename "||strip(name)||"_ch="||strip(name)||";";

*** create macro variables to convert drop and rename variables;

call symput("chg"||compress(put(_n_,best.)),var1);

call symput("drop"||compress(put(_n_,best.)),var2);

call symput("renm"||compress(put(_n_,best.)),var3);

if eof then call symput("cnt" , compress(put(_n_,best.)));

run;


%if &cnt>0 %then %do;

data SN&i_OUT_CHAR;

set SNOUT&i;

%do j=1 %to &cnt;

&&chg&j.;

%end;

%do j=1 %to &cnt;

&&drop&j.;

%end;

%do j=1 %to &cnt;

&&renm&j.;

%end;

run;

%end;


PROC SORT DATA=SNOUT&i;

BY Brand;

RUN;


PROC CONTENTS DATA=SNOUT&i;

RUN;

%END;

%mend;

New Contributor
Posts: 3

Re: Do Loop in macro not replacing the column name

thanks for your help. i learnt SAS for a week and trying to work on a assignment . I am a newbie.

I tweaked a little bit but now I get a different error pasted below.

%MACRO CHGFMT (i=);

%DO i=1 %TO 13;

j=i-1;

DATA SNOUT&i_CHAR;

set SNOUT&i;

RANK&i_Ch = strip(put(rank&i,3.));

RANK&j_Ch = strip(put(rank&j,3.));

DROP rank&i;

DROP rank&j;

RENAME rank&i =RANK&i_CH;

RENAME rank&j =RANK&j_CH;

RUN;

PROC PRINT DATA=SNOUT&i_CHAR;

RUN;

PROC CONTENTS DATA=SNOUT&i_CHAR;

RUN;

%END;

%MEND;

%CHGFMT;

i get syntax error after executing and the log doesn't give me a clear pic. Can anyone please help me

Thanks

ERROR: Expected close parenthesis after macro function invocation not found.

NOTE: Variable rank is uninitialized.

NOTE: Variable rank12 is uninitialized.

WARNING: The variable rank in the DROP, KEEP, or RENAME list has never been referenced.

WARNING: The variable rank12 in the DROP, KEEP, or RENAME list has never been referenced.

NOTE: There were 0 observations read from the data set WORK.SNOUT.

NOTE: The data set WORK.SNOUTCH has 0 observations and 8 variables.

NOTE: DATA statement used (Total process time):

      real time           0.03 seconds

      cpu time            0.01 seconds

Super Contributor
Super Contributor
Posts: 3,174

Re: Do Loop in macro not replacing the column name

Your use of &I and &J must also consider adjacent characters which SAS just might be treating as part of your SAS macro variable name, whether intended or not.  Look at using the period delimiter to avoid such SAS compilation / execution interpretation.

Along this discussion, a useful statement to help with SAS macro language diagnosis is this one:

%PUT My User-Assigned Macro Variables:  ;

%PUT _USER_;

It will help reveal all of your intended and unintended SAS macro variables generated by your SAS code piece / macro invocation.  As well, these are interesting to reveal SAS environment data about LOCAL and GLOBAL macro variables:

%PUT _LOCAL_;

%PUT _GLOBAL_;

And this one too:

%PUT SAS-maintained automatic macro variables are:

%PUT _AUTOMATIC_;

Scott Barry
SBBWorks, Inc.

Super User
Super User
Posts: 6,495

Re: Do Loop in macro not replacing the column name

Your macro has a number of problems.

1) You define it with a parameter I, but then inside the macro you use the same variable I in a %DO loop.  So the value passed into the macro is never used

2) Right after the %DO statement you have what looks like a data step assignment statement (j=i) but your macro is not generating a DATA step that could use such a statement.

3) You seem to be referring to macro variables such as I_CHAR, I_CH, J_CH and J that do not appear to have ever been created.

Super User
Posts: 6,927

Re: Do Loop in macro not replacing the column name

Make sure that the files are imported correctly. Everything else is just a stupid waste of time.

Learn to walk before you run; learn to crawl before you start walking,

As you have started with SAS a week ago, in SAS terms you're like a baby that has been placed on the living room carpet for a first crawling excursion. Macro language is clearly a part of the "walk" phase.

Solve the import problems first to create properly structured datasets; this will provide you with most of the insights you need right now.

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
Ask a Question
Discussion stats
  • 10 replies
  • 425 views
  • 1 like
  • 7 in conversation