CODEWITHSUNDEEP
csvsas
sasstudent
sasstudent

Reputation: 187

Writing dataset to different .csv with specific name using ODS

Edit 3: Solved. For efficiency purposes, I decided to loop through each secid one at a time, generate a "mydat-like" dataset, export it using proc export. This proved to be a lot more efficient than having mydat with all the data from all secids and then outputing it to different files using ODS. Thank you very much for your help @Joe and @scott, my solution was very much derived from the input of both of you.

Edit 2: By using ods csv I am able to output the different BY groups in my dataset, as long as they are sorted by secid it works well. The filenm field is useless and can be dropped. My issue now is the naming pattern of this solution. The different BY groups are outputted as csv11.csv, csv12.csv... Ideally the program should output the files with each secid.csv, e.g. 106276.csv. Does anybody know how to do this?

proc sort data=mydat;
     by secid;
run;

ods csv
    file="/export/csv11.csv"
    newfile=bygroup;
    proc print data=mydat;
        by secid;
    run;
ods csv close;

Edit: I am reading up on ODS CSV, as suggested by this page https://communities.sas.com/message/184651, particularly in the solution offered by Cynthia@sas

Original post: Following a former question (Error in data set firstobs), I have a follow up question. If I have a data set as the following, how can I output each line to its corresponding filenm? 

Obs     secid   date        impl_volatility  filenm
131040  106276  31DEC2003   0.24322          /export/mydat_106276.csv
131040  152120  31DEC2003   0.24142          /export/mydat_152120.csv

The case here is that I have a dataset with many records with different secids and I have generated the filenm field with the corresponding output file. In the previous question, @Joe had given me a solution for this but I changed the code a bit. Previously, his solution was:

data _null_;
    set mydat;
    file a filevar=filenm dlm='09'x lrecl=32767;
    put (_all_)($); 
    *or perhaps a more complex put statement - see what proc export generates;
run;

Where a was 1/0 dataset resulting from a data merge, as in (the following code is just an illustration of what a was):

data mydat;
    merge mydat1(in=a) mydat2(in=b)
    by secid;
run;

But I cannot get this solution to work after the code changes, I no longer have a data merge but rather a proc sql. Either way, I still have filenm in the data set, which should make this feasible.

Thank you in advance for your help.

Upvotes: 0

Views: 632

Answers (1)

scott
scott

Reputation: 2275

I don't know how to do what you want using ods csv, if someone else does, i'll delete this

But, this should create a csv for each secid with the name in your column filenm -This won't work if you have multiple rows for one secid

-If you don't want to use the filenm path, change the creation of the macro variable where I commented and change the path to include .csv

%macro csv_output();

    proc sql noprint;

        create table secid_dim as
        select distinct secid
        from mydat
        ;
    quit;

    data secid_dim;
        set secid_dim;
        id_num + 1;
        by secid;
    run;

    proc sql noprint;

        select distinct max(id_num)
        into :ttl_id
        from secid_dim
        ;
    quit;

    %do ii = 1 %to &ttl_id;
        proc sql noprint;

            select distinct secid
            into :sec_&ii.
            from secid_dim
            where id_num = &ii.
            ;
        quit;

        proc sql noprint;   

            select distinct filenm /*Change to secid if you do not want to use filename, but also change your macro variable call to include .csv*/
            into :file_&ii.
            from mydat
            where secid = &&sec_&ii..
            ;
        quit;

        proc sql noprint;

            create table _tempout_ as
            select *
            from mydat(drop=filenm)
            where secid = &&sec_&ii.
            ;
        quit;

        proc export data=_tempout_ outfile="D:/Scripts&&file_&ii.."
            dbms=csv replace;
        run;

    %end;
%mend;
%csv_output;

Upvotes: 0

Related Questions