BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
LB
Quartz | Level 8 LB
Quartz | Level 8

Hello all-

Happy Friday-

I am writing this out to get some general ideas on how to approach the problem.

 

Suppoded I have two companies-ABC and DEF, and with each a subset of variables-codes 

for example  

 

ABC G9

ABC G11

ABC G25

DEF G15

DEF G27

 

 

What I want to do is produce a text file for each company.i.e. ABC.text, DEF.text 

 

In each text file, I need to cycle through the codes in such a way that each file would have-

 

Hello world

Hello G9

 

Goodbye

 

Hello world

Hello G11

 

Goodbye

 

...

 

 

Hello world

Hello G2000

 

Goodbye

 

what I am doing is a lot more complicated but I think if I get the gist then I can carry on-

basically I am outputting a dynamic vba script.What I am really doing is outputting a bunch of statements. I can't include real data as it is highly sensitive-

 

The VBA script actually works as well as the VBS script-now I just need to macro the heck out of it.

 

What I can do is output individual files for each iteration but I do not want to do that.

 



%macro pdf(data=, var=, var1=,var2=,var3=);


   proc sort data=&data(keep=&var &var1 &var2 &var3) out=values nodupkey;
    by &var;
   run;
   data _null_;
      set values end=last;
      call symputx('site'||left(_n_),&var);
      call symputx('sitea'||left(_n_),&var1);
      call symputx('siteb'||left(_n_),&var2);
        call symputx('sitec'||left(_n_),&var3);
    
      if last then call symputx('count',_n_);
   run;



   

select(&var);
%do i=1 %to &count;



put "Attribute VB_Name = ""Module1"" ";
put "Sub &&site&i()";
put "ActiveSheet.Range(""&&sitec&i"").Select";
put "   ActiveSheet.OLEObjects.Add Filename:=""T:\P_PPR\PPR_Program\QOS\PPR\NCAL_CG_Files\Complaints Details\2015MY\&&sitea&i"", Link _";

put "        :=False, DisplayAsIcon:=True, IconFileName:= _ ";

put "        ""C:\windows\Installer\{AC76BA86-7AD7-FFFF-7B44-AB0000000001}\PDFFile_8.ico"", IconIndex:=0, IconLabel:= _";
 put "       ""&&siteb&i"" ";


        
put    "ActiveSheet.OLEobjects.Height = 60";
put   "ActiveSheet.OLEObjects.Width = 50";
put  "End Sub";

put fruit;
datalines;
end

;
run;


%end;


%mend;
%pdf(data=MED_ROS,var=MACRONAME,var1=pdf_file,var2=NAMEX,var3=PLACEHOLD);

 

 

 

 

 

 

 

 

 

 

1 ACCEPTED SOLUTION

Accepted Solutions
PaigeMiller
Diamond | Level 26

@Astounding

I think there's a much simpler way

 

data oink;
    file "external_file.txt";
    input id $ id2 $;
    if _n_=1 then put "Start World";
    put "Hello, World" / Hello" id2 $8. // "Goodbye";
    cards;
ABC G9
ABC G11
ABC G25
;
run;

 

 

--
Paige Miller

View solution in original post

9 REPLIES 9
PaigeMiller
Diamond | Level 26

I don't think a macro is needed here at all, a DATA step will do this. UNTESTED CODE

 

data oink;
    file "external_file.txt";
    input id $ id2 $;
    put "Hello, World" / Hello" id2 $8. // "Goodbye";
    cards;
ABC G9
ABC G11
ABC G25
;
run;
--
Paige Miller
LB
Quartz | Level 8 LB
Quartz | Level 8
PaigeMiller-
This works fairly well-One question though-
Let's say I want to add something at beginning or end
i.e.= "Start world " That would be the first line in the file but then not repeat?

Thanks for your assistance
Astounding
PROC Star

How about writing the DATA steps to a file?

 

data _null_;

set have;

file 'file_holding_all_the_SAS_code_needed.txt' noprint;

by company;

if first.company then put 'file "' company +(-1) '.txt" noprint;';

put 'put "Hello world";';

put 'put "Hello "' company +(-1) ';' ;

put 'put / "Goodbye";';

run;

 

data _null_;

%include 'file_holding_all_the_SAS_code_needed.txt';

run;

 

It's untested and gives you a headache just looking at it ... but it's at least a workable approach.

 

Good luck.

PaigeMiller
Diamond | Level 26

@Astounding

I think there's a much simpler way

 

data oink;
    file "external_file.txt";
    input id $ id2 $;
    if _n_=1 then put "Start World";
    put "Hello, World" / Hello" id2 $8. // "Goodbye";
    cards;
ABC G9
ABC G11
ABC G25
;
run;

 

 

--
Paige Miller
Astounding
PROC Star

Paige,

 

I added the complications because of one requirement ... creating a separate file for each company.  How do you create ABC.txt, DEF.txt, etc.?

ndp
Quartz | Level 8 ndp
Quartz | Level 8

To create a file for each company you can use macro:

%macro test();

proc sql noprint;

select distinct(company) into: _comlst separated by "#"

from indata;

select count(distinct(company)) into: _comnum

from indata;

quit;

 

%do i=1 %to &_comnum;

%let currcomp=%scan(&_complst.,&i,#);

data _null_;

set indata;

where company="&currcomp.";

file "&currcomp..txt";

...

...

...

run;

%end;

%mend;

PaigeMiller
Diamond | Level 26

@Astounding wrote:

Paige,

 

I added the complications because of one requirement ... creating a separate file for each company.  How do you create ABC.txt, DEF.txt, etc.?


 

 

The FILEVAR= option allows this in a datastep.

--
Paige Miller
Astounding
PROC Star

Ahhh, life gets simpler by the day.

LB
Quartz | Level 8 LB
Quartz | Level 8
Very nice-
Thank you!

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
  • 9 replies
  • 1714 views
  • 2 likes
  • 4 in conversation