BookmarkSubscribeRSS Feed
SagayamaryPriti
Calcite | Level 5

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

10 REPLIES 10
sbb
Lapis Lazuli | Level 10 sbb
Lapis Lazuli | Level 10

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.   

Astounding
PROC Star

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.

Reeza
Super User

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.

SagayamaryPriti
Calcite | Level 5

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;

Tom
Super User Tom
Super User

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.

ndp
Quartz | Level 8 ndp
Quartz | Level 8

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;

SagayamaryPriti
Calcite | Level 5

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

sbb
Lapis Lazuli | Level 10 sbb
Lapis Lazuli | Level 10

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.

Tom
Super User Tom
Super User

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.

Kurt_Bremser
Super User

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.

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 10 replies
  • 1393 views
  • 1 like
  • 7 in conversation