SAS Macro

[jacksonan123]

I have generated files ref1_1 and ref1_2  where the first number refers to the subject while the second is the repetition.  An example ref file is below and will have multiple columns depending upon the number of reps. The posted has 3 since &j=3.  What I would like to do is keep an1, Col&j, and treat where the column that is retained matches the &j (eg, for AREF1._1 an1,col1, and treat) whereas for AREF1._2 it would be an1,col2, and treat. I need to know how to create a macrovariable out of the columns so that the desired one can be put into the keep statement.

Example ref file.

an1	COL1	COL2	COL3	Treat
ka1	4.4E+08	0.649438	-0.22775	BREF
ka2	2E+09	1.004508	-0.17851	BREF
cl	8.35E+08	7.486796	6.484179	BREF
vf	5.19E+08	8.70274	7.372281	BREF
D1	92912286	2.528415	0.134371	BREF
lag	1.88E+08	1.673499	1.299389	BREF
logit	-1883756	0.333883	-0.71054	BREF

%MACRO bootR;
%do i=1% to 34;
%DO j=1% to 3;
DATA  AREF&i._&j;
set ref&i._&j;
Treat='BREF';
run;

DATA  BTES&i._&j;
set TES&i._&j;
Treat='ATES';

run;

PROC EXPORT DATA=aref1_1
            OUTFILE= '/folders/myfolders/IMLSUBGEN/ref1_1.csv' 
            DBMS=CSV REPLACE;
PROC EXPORT DATA=aref2_2
            OUTFILE= '/folders/myfolders/IMLSUBGEN/ref2_2.csv' 
            DBMS=CSV REPLACE;
RUN; 
%end;
%end;
%mend bootR;
%bootR;
run;

[PaigeMiller]

I need to know how to create a macrovariable out of the columns so that the desired one can be put into the keep statement.

Is your question how to extract the last digit of the file name (ref1_2) into a macro variable? Or is your question how to determine the number of columns from the contents of the data set?

[jacksonan123]

My question is how to keep the column that matches the &j for the AREF data
set from the ref&i,_&j data set in addition to the columns an1 and treat .
For an actual run there could be 1000 columns (i.e., col1 col2..col1000) and
&j for ref would be Do j=1% to 1000.

[PaigeMiller]

So, first you said "I need to know how to create a macrovariable out of the columns" and now you say "how to keep the column that matches the &j". These are not the same, and before I go ahead and write code, I want to be 100% sure I understand your request.

[jacksonan123]

I need to retain the desired columns. My initial thoughts was that it could
be best done via a macrovariable, but based upon you response and expertise
that would not be the way to go.

[PaigeMiller]

---

@jacksonan123 wrote:
I need to retain the desired columns. My initial thoughts was that it could be best done via a macrovariable, but based upon you response and expertise that would not be the way to go.

---

I never said this. I am still asking for clarification.

[jacksonan123]

I have 3 data sets named ref&i._&j where &i refers to the number of subj
while the &j refers to the repetition.

The data sets contain the following columns


an1

COL1

COL2

COL3

Treat



These ref data set will be set to aref&i._&j respective data sets.

For the data set aref1._1, I would like to retain the columns an1, col1, and
treat whereas for aref1._2 I would like to retain columns an1, col2, and
treat. For aref1._3 I would like to retain an1, col3, and treat. Hopefully
this will sufficiently clarify what I want.

[CarmineVerrell]

Hello, 

   Please find attached one possible solution.

Data ref1_1;
  infile cards;
  input ant1 $ col1 col2 col3 treat $;

cards;
ka1 4.4E+08 0.649438 -0.22775 BREF
ka2 2E+09 1.004508 -0.17851 BREF
cl 8.35E+08 7.486796 6.484179 BREF
vf 5.19E+08 8.70274 7.372281 BREF
D1 92912286 2.528415 0.134371 BREF
lag 1.88E+08 1.673499 1.299389 BREF
logit -1883756 0.333883 -0.71054 BREF
;
run;

%MACRO bootR;

%do i=1% to 34;
  %DO j=1% to 3;

   DATA AREF&i._&j(keep=ant1 col&j treat);
     set ref1_1;
      Treat='BREF';
   run;

/*Not sure what TES&i.&j is so commenting it out*/
/*DATA BTES&i._&j;*/
/*set TES&i._&j;*/
/*Treat='ATES';*/
/**/

/*run;*/

PROC EXPORT DATA=aref1_1
OUTFILE= '/folders/myfolders/IMLSUBGEN/ref1_1.csv'
DBMS=CSV REPLACE;
RUN;

PROC EXPORT DATA=aref2_2
OUTFILE= '/folders/myfolders/IMLSUBGEN/ref2_2.csv'
DBMS=CSV REPLACE;
RUN;

%end;

%end;

%mend bootR;

%bootR;

[jacksonan123]

This will not work because I will have in my actual runs %DO j=1% to 1000
which means that I would have to code the set ref1_1 many times up to set
ref1_1000 which is not practical.

[CarmineVerrell]

I am sorry but I don't understand, as the coding example i have provided gives you what you have described in a previous response(see below-response i am talking about). If it is not giving you the right results can you please clarify? Thanks so much.  

For the data set aref1._1, I would like to retain the columns an1, col1, and
treat whereas for aref1._2 I would like to retain columns an1, col2, and
treat. For aref1._3 I would like to retain an1, col3, and treat. Hopefully
this will sufficiently clarify what I want.

[jacksonan123]

EXTERNAL

In words this is my best explanation:

For the data set aref1._1, I would like to retain the columns an1, col1, and
treat whereas for aref1._2 I would like to retain columns an1, col2, and
treat. For aref1._3 I would like to retain an1, col3, and treat. Hopefully
this will sufficiently clarify what I want.

I will try again.

For each data set DATA  AREF&i._&j where &i refers to subject and &j refers to replicate.The data ref&i._&j which are replicate data sets for a subject in this case subject 1.  These will be set to AREF&i._&j dependent upon the &i and &j values.The data sets REF&i._&j (i.e., ref1_1 and ref1_2) contain the following columns an1, col1,col2, and col3For each data set the columns contain values (e.g., col2  

0.649438

1.004508

7.486796

8.70274

2.528415

1.673499

0.333883

)

For any given subjects  AREF1_1 and AREF1_2 I would like to have REF1_1 and REF1_2 set to those AREF files respectively.  The set files and the column to be kept are determined by the subject and the replication.  For AREF1_1 col1 is kept while for AREF1_2 col2 is kept.  In summary,  the col number corresponding to the &j value is kept.

Hopefully this is a clearer explanation.

[jacksonan123]

When I used your code:

%MACRO bootR;
%do i=1% to 34;
%DO j=1% to 3;
DATA AREF&i._&j(keep=an1 col&j);
set ref1_1;
Treat='BREF';

run;



I get the following errors in the log.



SYMBOLGEN: Macro variable J resolves to 3

22: LINE and COLUMN cannot be determined.

NOTE 242-205: NOSPOOL is on. Rerunning with OPTION SPOOL might allow
recovery of the LINE and COLUMN where the error has occurred.

ERROR 22-322: Syntax error, expecting one of the following: ;, (, DATA,
DBLABEL, DBMS, DEBUG, FILE, LABEL, OUTFILE, OUTTABLE,

REPLACE, TABLE, _DEBUG_.

SYMBOLGEN: Macro variable J resolves to 3

200: LINE and COLUMN cannot be determined.

NOTE: NOSPOOL is on. Rerunning with OPTION SPOOL might allow recovery of the
LINE and COLUMN where the error has occurred.

ERROR 200-322: The symbol is not recognized and will be ignored.

NOTE: PROCEDURE EXPORT used (Total process time):

real time 0.00 seconds

cpu time 0.00 seconds

[jacksonan123 · Accepted solution]

Using symput allows one to keep the desired column for each AREF&i._&j data sets and solves the post.

%MACRO bootR;
%do i=1% to 34;
%DO j=1% to 3;
DATA  AREF&i._&j(keep=an1 col&j treat);
set ref&i._&j;
call symput ("col&j", col1);
Treat='BREF';

run;