Your opportunity here will depend on your SAS experience level - to start.
Investigate using PROC IMPORT to read your Excel file and create a SAS data library member - here is a Google advanced search argument against the SAS.COM site for DOC and supplemental technical/conference reference material on the topic:
import excel file site:sas.com
Then after you have a SAS member, you will need to compose a SAS program to read your SAS member iteratively for each "date" using a WHERE statement and generate a separate SAS file for each date and export the selected data to a new (and uniquely named) Excel file -- here is a Google advanced search argument against the SAS.COM site for DOC and supplemental technical/conference reference material on the topic:
generate sas code site:sas.com
Within these DOC/website technical referencs below, you will see various options to choose, from PROC SQL using " INTO " and also using CALL EXECUTE to generate your SAS code for subsequent execution, either as a macro call/execution or as DATA and/or PROC step execution code.
Suggest you start with the DOC references below, develop a working program that reads and writes a single file, and then come back to the forum for input/feedback about how you can take that program and turn it into something that will be executed multiple times based on your input data "date" values.
It is not possible to wrap a DATA step around a macro execution request, nor is it possible to run a combination of SAS DATA step logic and macro logic as you have demonstrated.
You are on a reasonable track to generate a table with unique col2 values. That should be sufficient to use as input to a DATA step that invokes CALL EXECUTE passing a macro invocation command. And the macro invocation really only needs to pass the col2 variable value so you can reference that value in a WHERE statement to subset your primary input file (your sample file WORK.kamal).
From what logic you have demonstrated, I still don't see a working SAS program without involving any macro logic.
If you want a separate dataset for some other reason, then you will need to pursue some kind of macro approach.
However, if ALL you want to do is split the file based on COL2 values, you can simply do that with ODS and the NEWFILE=BYGROUP option.
NEWFILE= will create multiple ODS HTML, ODS CSV, ODS RTF or ODS PDF files based on the value that you provide to the option. If you have the file, work.kamal, shown above, one way to quickly and simply split the file for Excel is to do this:
** create work.kamal, as described above or using PROC IMPORT;
** Sort by "splitting" variable;
proc sort data=kamal;
** make a CSV file for every BY GROUP;
ods csv file='c:\temp\bygrp1.csv'
proc print data=kamal noobs;
var col1 col2;
ods csv close;
With this approach, if you have 3 distinct values for COL2, the NEWFILE= option would cause ODS to create 3 files: BYGRP1.CSV, BYGRP2.CSV and BYGRP3.CSV. If NEWFILE= finds a number in the beginning file name, then that number is incremented for every unique by group. The downside of this approach is that you do not have a SAS dataset for each separate by group -- but it seemed that the only purpose of your separate SAS datasets were to make files for Excel.