topic Re: How To Create Sub-Broker Report in SAS ?? in SAS Programming

How To Create Sub-Broker Report in SAS ??

Mohan_Reddy — Thu, 05 Mar 2020 08:59:45 GMT

I Have attached the output Report.....and 
inside i put the existing dataset..
So please anyone just saw the existing dataset and 
give a solution for this how to transform existing data into out output Report

Re: How To Create Sub-Broker Report in SAS ??

Kurt_Bremser — Thu, 05 Mar 2020 09:06:18 GMT

Provide example data in a data step with datalines,

NOT IN PICTURES!!

Re: How To Create Sub-Broker Report in SAS ??

Kurt_Bremser — Thu, 05 Mar 2020 09:07:44 GMT

Also show the code you already tried, and describe where it does not meet your expectations.

Re: How To Create Sub-Broker Report in SAS ??

Mohan_Reddy — Thu, 05 Mar 2020 09:09:16 GMT

Actually i got in CSV and Excel files only so after import into SAS i send that pictures...For your i am provide that CSV and Excel files here please find it.

Re: How To Create Sub-Broker Report in SAS ??

Mohan_Reddy — Thu, 05 Mar 2020 09:37:26 GMT

I had provide 5 csv files please find another one excel file...I provide just justification about this please find that also..

Edit by KB, text from file:

Report Name:
SUB-BROKER ACTIVE AM REPORT

Task
Data is available in different csv files like

Inwards day data is available in RAD1.CSV
NCA day data is available in RAD2.CSV

Inwards month data is available in RAD4.CSV
NCA mont data is available in RADD6.CASV

Inwards last month data is available in RAD3.CSV
NCA last month data is avilable in RAD5.CSV


Extract all the excel files into SAS
and manage the data according to your output report.

create macros for date file and give dates and monts dynamically

your output report should match 100% in all aspects like data & formatting  with output what i given.

Re: How To Create Sub-Broker Report in SAS ??

Kurt_Bremser — Thu, 05 Mar 2020 09:15:02 GMT

So show what you have already done, in code, to get from those csv files to your expected result.

Re: How To Create Sub-Broker Report in SAS ??

andreas_lds — Thu, 05 Mar 2020 09:16:55 GMT

Because you didn't ask for help importing the csv-files: Please post the code you are using to read ALL this files, and include the code required to get to the point where you actually are. And by "post" i mean as text using the little running man icon, NOT as attachments!

Re: How To Create Sub-Broker Report in SAS ??

Mohan_Reddy — Thu, 05 Mar 2020 09:17:06 GMT

I Can't understand how this existing data transformed into output report..please give a solution for this ....

Re: How To Create Sub-Broker Report in SAS ??

Mohan_Reddy — Thu, 05 Mar 2020 09:18:46 GMT

proc import datafile="C:\Users\Coputer15\Desktop\Sub-Broker active AM Report\RAD1.csv" out=work.RAD1
dbms=csv replace;
run;

proc import datafile="C:\Users\Coputer15\Desktop\Sub-Broker active AM Report\RAD2.csv" out=work.RAD2
dbms=csv replace;
run;

proc import datafile="C:\Users\Coputer15\Desktop\Sub-Broker active AM Report\RAD3.csv" out=work.RAD3
dbms=csv replace;
run;

proc import datafile="C:\Users\Coputer15\Desktop\Sub-Broker active AM Report\RAD4.csv" out=work.RAD4
dbms=csv replace;
run;

%macro import(file,dname);
proc import datafile="&file" out=&dname
dbms=excel replace;
getnames=yes;
run;
%mend;

%import(C:\Users\Coputer15\Desktop\Sub-Broker active AM Report\RAD6.xls,RAD6);

Re: How To Create Sub-Broker Report in SAS ??

Mohan_Reddy — Thu, 05 Mar 2020 09:21:20 GMT

These for import 5 CSV files into SAS

proc import datafile="C:\Users\Coputer15\Desktop\Sub-Broker active AM Report\RAD1.csv" out=work.RAD1
dbms=csv replace;
run;

proc import datafile="C:\Users\Coputer15\Desktop\Sub-Broker active AM Report\RAD2.csv" out=work.RAD2
dbms=csv replace;
run;

proc import datafile="C:\Users\Coputer15\Desktop\Sub-Broker active AM Report\RAD3.csv" out=work.RAD3
dbms=csv replace;
run;

proc import datafile="C:\Users\Coputer15\Desktop\Sub-Broker active AM Report\RAD4.csv" out=work.RAD4
dbms=csv replace;
run;

proc import datafile="C:\Users\Coputer15\Desktop\Sub-Broker active AM Report\RAD5.csv" out=work.RAD5
dbms=csv replace;
run;

these code for import 1 excel file into SAS.

%macro import(file,dname);
proc import datafile="&file" out=&dname
dbms=excel replace;
getnames=yes;
run;
%mend;

%import(C:\Users\Coputer15\Desktop\Sub-Broker active AM Report\RAD6.xls,RAD6);

please write a programme how to get that required output...

Re: How To Create Sub-Broker Report in SAS ??

Kurt_Bremser — Thu, 05 Mar 2020 10:26:44 GMT

So you first need to join all those imported tables, and get them into the right order for the report. Do that first. You can calculate the differences in this step, and assign proper labels to all your columns.

(Joining tables is very simple SAS technique and can't be a problem if you already advanced to complex reporting)

Then you should start experimenting with proc report, using the relevant columns for grouping, and the numeric columns for analysis.

Re: How To Create Sub-Broker Report in SAS ??

ballardw — Thu, 05 Mar 2020 20:14:26 GMT

@Mohan_Reddy wrote:
These for import 5 CSV files into SAS

proc import datafile="C:\Users\Coputer15\Desktop\Sub-Broker active AM Report\RAD1.csv" out=work.RAD1
dbms=csv replace;
run;

proc import datafile="C:\Users\Coputer15\Desktop\Sub-Broker active AM Report\RAD2.csv" out=work.RAD2
dbms=csv replace;
run;

proc import datafile="C:\Users\Coputer15\Desktop\Sub-Broker active AM Report\RAD3.csv" out=work.RAD3
dbms=csv replace;
run;

proc import datafile="C:\Users\Coputer15\Desktop\Sub-Broker active AM Report\RAD4.csv" out=work.RAD4
dbms=csv replace;
run;

proc import datafile="C:\Users\Coputer15\Desktop\Sub-Broker active AM Report\RAD5.csv" out=work.RAD5
dbms=csv replace;
run;

these code for import 1 excel file into SAS.

%macro import(file,dname);
proc import datafile="&file" out=&dname
dbms=excel replace;
getnames=yes;
run;
%mend;

%import(C:\Users\Coputer15\Desktop\Sub-Broker active AM Report\RAD6.xls,RAD6);

please write a programme how to get that required output...

Given your proc import code I am going to bet that you have none of the AM_CODE or TM_CODE values of OTHER that appear in your data. You may also not have any OTHER value for STATE_HEAD_CODE. I suspect that you may have different lengths for the AM_NAME, SH_NAME and TM_NAME variables in the different data sets. Which can lead to issues when the data is combined as well. And depending on the actual data files (I didn't look at all of them) there is a chance that you have a couple of variables that are numeric in one set and character in another.

Have you looked at proc contents for your imported data sets to verify that variables have the same type and length so there won't be problems when combining the data?