topic Issue converting text monthly to weekly values in SAS Programming

Issue converting text monthly to weekly values

PIEFLAVOR — Thu, 25 Jan 2024 02:07:07 GMT

I am trying to convert a text response containing monthly to a weekly value. The issue is when I run the code below it divides all values in var2 instead of just the ones that meet the text response requirements (e.g. 2 month) . Hopefully this makes sense, please let me know if my issue is unclear.

data have;

ID	var1	var2
1	2 months	150
2	two months	90
3	1 week	30
4	1 week	45
5	2 months	100
6	2 months	220

Desired outcome=

ID	var1	var2
1	1 week	17.26
2	1 week	10.36
3	1 week	30
4	1 week	45
5	1 week	11.5
6	1 week	25.31

Current code

data want;

set have;

if index(lowcase(var1), '2 month') > 0 then var2 = var2/8.69;

else if index(lowcase(var1), '2 months') > 0 then var2 = var2/8.69;

else if index(lowcase(var1), 'two months') > 0 then var2 = var2/8.69;

run;

Re: Issue converting text monthly to weekly values

Patrick — Thu, 25 Jan 2024 01:50:37 GMT

Your question will become much clearer if you provide us with sample HAVE data and show us the desired result.

The code you shared returns the result you desire as far as I understand your question.

data have;
  infile datalines truncover dlm=',';
  input var1 $50.;
  var2=8.69049;
  datalines;
xxx 2 Month yyy
xxx 2 Months yyy
xxx two Months yyy
xxx 3 Months yyy
;

data want;
  set have;
  if index(lowcase(var1), '2 month') > 0 then var2 = var2/8.69049;
  else if index(lowcase(var1), '2 months') > 0 then var2 = var2/8.69049;
  else if index(lowcase(var1), 'two months') > 0 then var2 = var2/8.69049;
run;

proc print data=want;
run;

Re: Issue converting text monthly to weekly values

Astounding — Thu, 25 Jan 2024 02:07:26 GMT

Note that you don't need to look for:
"2 months"
Any time it appears, it would have been caught by the first check, looking for:
"2 month"

Re: Issue converting text monthly to weekly values

PIEFLAVOR — Thu, 25 Jan 2024 02:12:34 GMT

I forgot to include my current results (undesired)-

ID	var1	var2
1	1 week	17.26
2	1 week	10.36
3	1 week	3.45
4	1 week	5.17
5	1 week	11.5
6	1 week	25.31

Re: Issue converting text monthly to weekly values

ballardw — Thu, 25 Jan 2024 04:20:28 GMT

I am not sure that I see a clear definition of a conversion from "month" to week. This may be very important if you have values of 3 or more months. I see one conversion for only 2 months. Do you have more variability in your data?

Re: Issue converting text monthly to weekly values

mkeintz — Thu, 25 Jan 2024 06:00:59 GMT

I guess you assume weeks per month = 4.34523809523809 (= 365/*(7*12)).

If you have only two original time units ("week/weeks" or "month/months") then discriminating between them is easy. The remaining task is to get a numeric value from the first "word" in var1. You could then do:

data have;
  input var1 $12.   var2;
datalines;
2 months     150
two months   90
1 week       30
1 week       45
2 months     100
2 months     220
run;

%let weeks_per_month=%sysevalf((365/7)/12);

data want (/*drop=_:*/);
  set have;
  array numwords {12} $6 _temporary_ ('one','two','three','four','five','six',
                                      'seven','eight','nine','ten','eleven','twelve');
  _nunits=whichc(lowcase(scan(var1,1)),of numwords{*});
  if _nunits=0 then _nunits=input(scan(var1,1),2.);

  if index(lowcase(var1),'month') then weekly_var2=  var2/(_nunits*&weeks_per_month);
  else weekly_var2=var2/_nunits;
run;

This allows you to have "one month", "two months", ... "twelve months" to start var1 (or "one week" through "twelve weeks"), as well as any set of (up to) 12 digits.