Solved

SAS Data Sets Manipulations

Posted on 2010-08-19
4
855 Views
Last Modified: 2013-11-16
I am trying to do the following:

Process:
1. a) Create ASCII file "file_<var1>.DAT" (FILE1).
          Record type is fixed.
          Each record has a carriage return.
     b) Keep all records from the SAS Dataset "data1".
     c) Output all records
 
2. Create ASCII file "file2_<var1>.DAT" (FILE2).
       Record type is fixed.
       Each record has a carriage return.
     b) Keep all records from the SAS Dataset "data2".
     c) Output all records

3. Create file "LIST_<var1>.LIS"
      Provide the count of records written to "file1_<var1>.DAT"  and "file2_<var1>.DAT" by "var2" by "var3".


Everything is working fine in the below code, but one thing:

My output file "file_&var1.DAT" only contains the last record' value from "var1", - all other values are missed or not recorded.

Here is what I am trying to get:
For every different value of "var1" in the "data" SAS Dataset I am trying to output file "file_&var1.DAT.
So, if SAS dataset "data1" has total of 5 records and values for "var1" are for example 09, 09, 67, 78, 98, than I should have total of 4 "file_&var1.DAT" files as my output:
file_09.DAT
file_67.DAT
file_78.DAT
file_98.DAT

Right now for my output file I am only getting  file_98.DAT, which is my last record' value, which in this case is 98.
How come all other values for "var1" are not recorded and output files are not made?
I understand that I need to create a loop to create multiple output files, but I do not know how?!? Where does this loop go and what is the structure of this loop?
Thank you in advance!

Here is my code:

libname dir 'directory';

data _null_;
 set dir.data1;
   call symput('var1',oldvar5);
run;

%MACRO START;

proc sort data=dir.data1 out=dir.sdata1;
   by var3;
run;

data per.var3_cnt (keep=var3 Total);
 set per.sdata1;
  by var3;
 if first.var3 then Total= 0;
 Total+1;
 if last.var3;
run;
 
proc sort data=dir.data1 out=dir.sdata2;
   by var2;
run;

data dir.var2_cnt (keep=var2 Total);
 set dir.sdata2;
  by var2;
 if first.var2 then Total=0;
 Total+1;
 if last.var2;
run;

data _null_;
 file "dir:file1_&var1..DAT";
 set dir.sdata1(rename=(var6=oldvar6 var7=oldvar7 var2=oldvar2));
   put
      @1 oldvar2  $char2.
      @3 var3       $char1.
      @4 oldvar5  $char2.
      @6 oldvar6  $char2.
      @8 oldvar7  $char2.;
run;

proc sort data=dir.data2 out=dir.sdat3;
  by var3;
run;

data per.var3_cnt2 (keep=var3 Total);
 set dir.sdata3;
  by var3;
 if first.var3 then Total=0;
 Total+1;
 if last.var3;
run;

proc sort data=dir.data2 out=dir.sdat4;
  by var2;
run;

data per.var2_cnt2 (keep=var2 Total);
 set dir.sdata2;
  by var2;
 if first.var2 then Total=0;
 Total+1;
 if last.var2;
run;

data _null_;
 file "dir:file2_&var1..DAT";
 set dir.sdata2 (rename=(var2=veryoldvar2));
   put
      @1 veryoldvar2  $char2.
      @3 var3              $char1.
      @4 veryoldvar6  $char2.
      @6 veryoldvar7  $char2.;
run;

title1 '************START COUNTS**************';
title2 'Count of Records in FILE1 by VAR3';
proc printto print="LIST_&var1..LIS";
run;

proc print noobs data=var3_cnt;
run;

title1 'Count of Records in FILE1 by VAR2';
proc print noobs data=var2_cnt;
run;

title1 'Count of Records in FILE2 by VAR3';
proc print noobs data=var3_cnt2;
run;

title1 'Count of Records in FILE2 by VAR2';
proc print noobs data=var2_cnt2;
run;

proc printto;
run;

data _null_;
 file "dir:LIST_&var1..LIS" MOD;
 put "****************END OF COUNTS************";
run;

%MEND STARTS;
%STARTS;
0
Comment
Question by:labradorchik
4 Comments
 
LVL 14

Assisted Solution

by:Aloysius Low
Aloysius Low earned 150 total points
Comment Utility
for a start, to answer why you only generate 1 file which matches the value of the last record, it's because you did not loop the dataset to create a new file.

what you did is:
data _null_;
 set dir.data1;
   call symput('var1',oldvar5);
run;
this will loop data1 and starting from the first record, read the value into global variable var1. hence at the end of this step, you have the value from the last record.

what you should do is actually to loop as shown below. the rest of the things you are supposed to do i have skipped them, at least so that you can see the 4 different files.

%macro loop;
  proc sql;
    select count(*) into :row_count from dir.data1;
  quit;

  data _null_;
    set dir.data1;
    call symput('var1'||compress(_n_),oldvar5);
  run;

  %do i=1 %to &row_count;
    data _null_;
      file "dir.file_&&var1&i..DAT";
        set [dataset]
        [fields to output]
      run;
  %end;

%mend;
%loop;
0
 
LVL 11

Accepted Solution

by:
theartfuldazzler earned 200 total points
Comment Utility
Hi

An alternative if you want to run the macro once per record would be to use CALL EXECUTE function:

**** start ****;


%MACRO STARTS(var1);

**** Code that you want to run against VAR1 ****;

%MEND STARTS;

data _null_;
 set dir.data1;
   CALL EXECUTE('%STARTS('||OldVar5||');');
run;
**** end ****;

This will run the macro STARTS 4 times, with different &var1 each time.

*** Your second question ***;
For your "counts", I would use a summary procedure - either PROC SUMMARY, or PROC SQL.  I happen to prefer PROC SQL:


**** Start ****;
PROC SQL;
  create table per.var2_cnt2 as
  select var2, count(*) as freq label = 'COUNT' from  dir.sdata2
group by var2;


*** end ***;
... but your method should work just as well.

0
 
LVL 3

Assisted Solution

by:radevo
radevo earned 150 total points
Comment Utility
Here is a complete solution.
A sorted view of the data is passed _once_ through a data _null_ that dynamically changes the output file, via the filevar= option,  to match the current discriminating (or by) variable.

data class1;
  set sashelp.class;
run;

data class2;
  set sashelp.class;
run;


%macro SplitIntoTexts (data=, by=, putMacro=, outFolder=, filenamePrefix=, logOption=);

filename splitlog "&outFolder.\file_outcounts.dat" &logOption;

proc sql;
  create view mydata as
  select * from &data
  order by &by;
quit;

data _null_ ;
  declare hash values(ordered:'a');
  values.defineKey("&by");
  values.defineData("&by", 'count');
  values.defineDone();

  do until (end);
    set mydata end=end;

    if values.find() ne 0 then count = 0;
    count + 1;
    values.replace();

    filename = cats ("&outfolder.\&filenamePrefix.",&by,".dat");

    file dynamic filevar=filename;
    %&putMacro.;
  end;

  file splitlog;

  declare hiter keys('values');
  do while (keys.next() eq 0);
    filename = cats ("&outFolder.\&filenamePrefix.",&by,".dat");
    put count 'records output to ' filename;
  end;

  stop;
run;
%mend;

%macro putFile1;
    put
      @1  name $char8.
      @9  sex  $char1.
      @10 age  2.
      @12 height 4.0
      @16 weight 3.
    ;
%mend;

%macro putFile2;
    put
      @1  name $char8.
      @9  sex  $char1.
      @10 age  2.
      @12 height 4.0
      @16 weight 3.
    ;
%mend;

options mprint;

%SplitIntoTexts (data=class1, by=age, putMacro=putFile1, outFolder=c:\temp, filenamePrefix=file1_, logOption=);
%SplitIntoTexts (data=class2, by=sex, putMacro=putFile2, outFolder=c:\temp, filenamePrefix=file2_, logOption=mod);

0
 

Author Comment

by:labradorchik
Comment Utility
Hi all,
Thank you very much for all your inputs!! I really appreciated!!

lowaloysius,
Your code was helpful, but I could not use it due to a few requirements that I had after you posted your code.

radevo:
I started to follow your example, but your codding procedures became a little complicated for me with all three macro statements, but your code makes sense to me!

theartfuldazzler:
I went ahead with your code because I really like how you used CALL EXECUTE and PROC SQL!


Thanks to all of you for help!!




0

Featured Post

Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

Join & Write a Comment

Entering a date in Microsoft Access can be tricky. A typo can cause month and day to be shuffled, entering the day only causes an error, as does entering, say, day 31 in June. This article shows how an inputmask supported by code can help the user a…
Shadow IT is coming out of the shadows as more businesses are choosing cloud-based applications. It is now a multi-cloud world for most organizations. Simultaneously, most businesses have yet to consolidate with one cloud provider or define an offic…
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

763 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now