How to Create Custom Column values from existing date field.

[PrudhviB]

Hi Community,

I am trying to create a new Column with custom values from existing records in the data set. I am finding it difficult when I want to combine date and a text format as a column. 

I have included the sample data for the reference, any help is much appreciated. 

my approach:

I used Case function to created tags (under column want)  was unsuccessful for wk of May2nd on wards. 

(CASE
WHEN t1.week_end BETWEEN '01JUN2019'd AND '30JUN2019'd THEN 'LegacySoftW'
WHEN t1.week_end BETWEEN '01JUN2021'd AND '30JUN2021'd THEN 'NO_legacySoft'
ELSE
END)

My data start from june 3rd 2019 and it goes on, ideally i want to have a column generated using the date field to create a tag under the new column (Column_want) with the format "wk of month(week_start) day(week_start)".

hope this is clear. 

Product_ID	Week_start	Week_end	Sales	Column_want(Tag)
500024	3-Jun-19	7-Jun-19	15	LegacySoftw
500026	10-Jun-19	14-Jun-19	20	LegacySoftw
500028	17-Jun-19	21-Jun-19	2	LegacySoftw
500030	24-Jun-19	28-Jun-19	65	LegacySoftw
500031	24-May-21	28-May-19	0
500032	31-May-21	4-Jun-21	5
500034	7-Jun-21	11-Jun-21	7	No _LegacySoft
500036	14-Jun-21	18-Jun-21	54	No _LegacySoft
500038	21-Jun-21	25-Jun-21	5	No _LegacySoft
500040	28-Jun-21	2-Jul-21	32	No _LegacySoft
500042	2-May-22	6-May-22	54	Wk of May 2nd
500044	9-May-22	13-May-22	58	Wk of May 9th
500046	16-May-22	20-May-22	84	Wk of May 16th
500048	23-May-22	27-May-22	6	Wk of May 23th
500050	30-May-22	3-Jun-22	87	Wk of May 30th
500052	6-Jun-22	10-Jun-22	58	Wk of June 6th

[Patrick]

Your else case needs to define the blank and you also need to define the column name for storing the values.

CASE
WHEN t1.week_end BETWEEN '01JUN2019'd AND '30JUN2019'd THEN 'LegacySoftW'
WHEN t1.week_end BETWEEN '01JUN2021'd AND '30JUN2021'd THEN 'NO_legacySoft'
ELSE ' '
END as want

Given your sample data: Would that be above logic and then everything after June 30, 2021 generates these week strings?

[PrudhviB]

@Patrick that is correct, anything after June 30th 2021 I want it to create that format of sting with the week start date. 

[Patrick · Accepted solution]

Below code getting you very close to what you're asking for.

You will have to re-assess your condition WHEN t1.week_end BETWEEN '01JUN2021'd AND '30JUN2021'd THEN 'No_legacySoft'   because based on your sample data the end date should actually be '02Jul2021'd

data have;
  infile datalines truncover dsd dlm='|';
  input
    Product_ID    
    Week_start:date.
    Week_end:date.
    Sales 
    Column_Want :$40.
    ;
  format week_start week_end date9.;
  datalines;

500024|3-Jun-19|7-Jun-19|15|LegacySoftw
500026|10-Jun-19|14-Jun-19|20|LegacySoftw
500028|17-Jun-19|21-Jun-19|2|LegacySoftw
500030|24-Jun-19|28-Jun-19|65|LegacySoftw
500031|24-May-21|28-May-19|0| 
500032|31-May-21|4-Jun-21|5| 
500034|7-Jun-21|11-Jun-21|7|No _LegacySoft
500036|14-Jun-21|18-Jun-21|54|No _LegacySoft
500038|21-Jun-21|25-Jun-21|5|No _LegacySoft
500040|28-Jun-21|2-Jul-21|32|No _LegacySoft
500042|2-May-22|6-May-22|54|Wk of May 2nd
500044|9-May-22|13-May-22|58|Wk of May 9th
500046|16-May-22|20-May-22|84|Wk of May 16th
500048|23-May-22|27-May-22|6|Wk of May 23th
500050|30-May-22|3-Jun-22|87|Wk of May 30th 
500052|6-Jun-22|10-Jun-22|58|Wk of June 6th
;

proc sql;
/*  create table want as*/
  select
    *,
    CASE
      WHEN t1.week_end BETWEEN '01JUN2019'd AND '30JUN2019'd THEN 'LegacySoftW'
      WHEN t1.week_end BETWEEN '01JUN2021'd AND '30JUN2021'd THEN 'No_legacySoft'
      when t1.week_end > '30JUN2021'd then catx(' ','Wk of'
                                          ,put(Week_start,monname.), put(Week_start,day.))
      ELSE ' '
      END as Column_Derived
  from have t1
  ;
quit;

[Reeza]

I would first create a format for the ordinal part of the day.

data ordinal_format;
	length label $8.;
  do number=1 to 31;
    numstring = put(number, 16. -l);
    if prxmatch('/(?<!1)1\s*$/',numstring) then ending='st';
    else if prxmatch('/(?<!1)2\s*$/',numstring) then ending='nd';
    else if prxmatch('/(?<!1)3\s*$/',numstring) then ending='rd';
    else ending = 'th';
    ordstring =catt(numstring, ending);

	  start=number;
	  label=ordstring;
	  fmtname="ordinal_fmt";
	  type="N";
	  output;
  end;


	keep start label fmtname type;
run;

proc format cntlin=ordinal_format;
run;

Then modify your CASE statement as follows:

else when t1.week_end > '30June2021'd then 
  cat('Wk of ', put(t1.weekend, monname.), ' ', put(day(t1.weekend), ordinal_fmt.))

 EDIT: months can have 31 day so updated format to have 31 days.

[PrudhviB]

Thank you @Reeza this one also worked.