topic Re: Tagging Columns column-wise with keywords before exporting File. in SAS Programming

Tagging Columns column-wise with keywords before exporting File.

Kirito1 — Wed, 05 Apr 2023 09:35:22 GMT

I was working on extracting a data using SAS. But, after I was able to deliver data user said. We want tagging with some of the columns. So, I wanted to know How can I do the following.

Below is a small SS of the data I exported after all the process in SAS.

COD_CUST	TRN_AMT_FEB23_TOT	TRN_COUNT_FEB23_TOT	TRN_AMT_JAN23_TOT	TRN_COUNT_JAN23_TOT	LAST_2_MNTH_AMT	LAST_2_MNTH_COUNT	COD_CC_BRN	COD_PROD	COD_ACCT_NO	COD_ACCT_STAT
21145070	376530	685	1519040	2824	1895570	3509	2404	20216	2.22E+15	8
21240214	153180	346	148422	332	301602	678	2208	20215	2.22E+15	8
21246886	268760	465	313685	563	582445	1028	2206	20216	2.12E+15	8
21579802	345407	534	227230	610	572637	1144	2215	20210	1.72E+15	8
21602186	13140	18	307580	128	320720	146	2243	20210	2.02E+15	8
21912931	66360	58	271760	89	338120	147	2273	20217	2.12E+15	8
22128229	112730	252	167050	341	279780	593	2189	20215	1.72E+15	8
22128232	122260	280	137520	289	259780	569	2189	20216	1.72E+15	8

Now, What they want is to tag some particular columns with Some keywords they Have given me.

For Example: First columns which is COD_CUST should be marked as PQ1_PQ, 3rd Column should be marked as PQ2_PQ and So on according to the keywords they given I have to mark the columns.

Below attached is the SS of what they actually want(although I could do this task on Excel itself but if there is a way I could do this Please, suggest as it would make the task easier as I have to perform this monthly)

PQ1_PQ		PQ2_PQ				PQ4_PQ			PQ13_PQ	PQ6_PQ
COD_CUST	TRN_AMT_FEB23_TOT	TRN_COUNT_FEB23_TOT	TRN_AMT_JAN23_TOT	TRN_COUNT_JAN23_TOT	LAST_2_MNTH_AMT	LAST_2_MNTH_COUNT	COD_CC_BRN	COD_PROD	COD_ACCT_NO	COD_ACCT_STAT
21145070	376530	685	1519040	2824	1895570	3509	2404	20216	2.22E+15	8
21240214	153180	346	148422	332	301602	678	2208	20215	2.22E+15	8
21246886	268760	465	313685	563	582445	1028	2206	20216	2.12E+15	8
21579802	345407	534	227230	610	572637	1144	2215	20210	1.72E+15	8
21602186	13140	18	307580	128	320720	146	2243	20210	2.02E+15	8
21912931	66360	58	271760	89	338120	147	2273	20217	2.12E+15	8
22128229	112730	252	167050	341	279780	593	2189	20215	1.72E+15	8
22128232	122260	280	137520	289	259780	569	2189	20216	1.72E+15	8

Your help and contribution is much appreciated.

Thanks in advance for all the contributors.

Re: Tagging Columns column-wise with keywords before exporting File.

LinusH — Wed, 05 Apr 2023 09:47:03 GMT

I wonder how will they use those tags (and the exported data, is a report, or will they continue to work wtih the data in pivot etc?)?

The export format is csv, Excel?

What you quite easily is to extend the columns names or use SAS labels and have that exported. But then the "tag" will end up in the same Excel cell.

Re: Tagging Columns column-wise with keywords before exporting File.

Kirito1 — Wed, 05 Apr 2023 10:54:01 GMT

These tags are actually policy code indicators of some policy which they have already defined.
And Yes the format in which the report I generate is in CSV format.
Extending the column names won't serve the purpose of what those tags/labels are doing.

For Example: I have a column(Total 197 columns) of CUSTOMER_TYPE..........Now for that Column I have to tag it PG7_19 Now This code indicates why we have only consider these types of customers only. SO, this is how this works.

I think this will make some sense now.

There are many more tagging's like this for the people who are reading the data for the first time So that they can consult policy without an confusion to clear their doubts.

Re: Tagging Columns column-wise with keywords before exporting File.

PaigeMiller — Wed, 05 Apr 2023 11:03:28 GMT

As far as I know, such "keywords" do not exist in CSV files, and even if you could manually modify the text of the CSV file to look the way you want, it would no longer be usable as a CSV file (again, as far as I know). This type of adding "keywords" can be done if you are willing to output directly to Excel.

Re: Tagging Columns column-wise with keywords before exporting File.

LinusH — Wed, 05 Apr 2023 11:55:21 GMT

As @PaigeMiller says, you can't really use acsv file structured that way.

The closest you can get with csv format is to add your tag to the column name, or concatenate them in the SAS label, and use that as columns header in the csv file.

And if it's a report, why use csv? csv is for "raw" data.

Re: Tagging Columns column-wise with keywords before exporting File.

andreas_lds — Wed, 05 Apr 2023 12:10:23 GMT

Assuming that you have a file, readable by sas, containing variable names an the keyword:

Export the dataset to a temp-file.
Read the variable-keyword-list.
Use a data step to process that dataset to write the row of keywords to the file
Use another data step to read the temp-file and append that to the export file

And here in an example using sashelp.cars:

%let temp_export = %sysfunc(pathname(work))\temp_export.csv;


/* Step 1 */
proc export data=sashelp.cars file="&temp_export." dbms=csv replace;
run;

/* Step 2: Dummy Variable-Key-"File" */
data work.keywords;
   length Variable $ 32 Key $ 42;
   informat Variable $upcase.;
   input Variable Key;
   datalines;   
Make key_one
DriveTrain key_two
Cylinders key_three
Weight key_four
;

/* Step 3 needs proc sql + data _null */
proc sql noprint;
   create table work.Vars as
      select upcase(Name) as Variable 
         from dictionary.columns
            where libname = 'SASHELP' and MemName = 'CARS'
            order by VarNum
    ;
quit;


%let export_file = INSERT_YOUR_PATH\tagged_export.csv;

data _null_;
   set work.Vars;
   file "&export_file";
   
   if _n_ = 1 then do;
      if 0 then set work.keywords;
      
      declare hash keys(dataset: 'work.keywords');
      keys.defineKey('Variable');
      keys.defineData('Key');
      keys.defineDone();
   end;
   
   if keys.find() = 0 then do;
      put Key +(-1) ',' @;
   end;
   else do;
      put ' ,' @;
   end;
run;


/* Step 4: Add the temp. export to the file written in the step before */
data _null_;
   infile "&temp_export";
   file "&export_file" mod;
   
   input;
   put _infile_;
run;