I am not too sure about better ways to deal with the Yearcount macro variable but I also would say that you are assigning new values to the same macro variable each time. Someone else may be better placed to advise on this though ... View more

Hi,   Part of the problem is the structuring of the if clauses.   if accyr_fy=2019 and accmth_fy<=12 and lossyr_fy=2019-0 or accyr_fy=2019 and lossyr_fy=2019-0 then %let Yearcount=0;   should really be rewritten as   if (accyr_fy=2019 and accmth_fy<=12 and lossyr_fy=2019-0) or (accyr_fy=2019 and lossyr_fy=2019-0) then %let Yearcount=0;   using parentheses. This may not be programatically necessary in every case but it also makes it much easier to read and debug when you can locate 'sections' in each clause. However there is a further problem here where the part after the 'or' effectively overrules the part before. Any obersavtions that satisfy (if accyr_fy=2019 and accmth_fy<=12 and lossyr_fy=2019-0) will also automatically satisfy (accyr_fy=2019 and lossyr_fy=2019-0). So this whole statement effectively is the same as saying.   if accyr_fy=2019 and lossyr_fy=2019-0 then %let Yearcount=0;     ... View more

I assume from the . values that this is a numeric variable? The .0 values look a bit unusual which is why I am asking.   try this   data work.sampsys1; set work.sampSys; if GHQ12 = .0 then GHQ12 = 0; If GHQ12 = 0 then GHQ3 = 1; else If 0 < GHQ12 < 3 then GHQ3 = 2; else GHQ3 = 3; run; That should at least tell us if the .0 is causing the problem ... View more

there also seems to be a .0 in the frequency table which may need addressing ... View more

I would rewrite the program as follows   data work.sampsys1; set work.sampSys; If GHQ12 = 0 then GHQ3 = 1; else If 0 < GHQ12 < 3 then GHQ3 = 2; else GHQ3 = 3; run; and that should be programatically correct.   Unless the error is to do with code you haven't provided it shouldn't occur now. ... View more

Well assuming the delimiters that seperates the words in each address are consistent then   data address; input add $ 1-50; datalines; 1 top road london england 2 low drive perth scotland 3 high street perth australia ; run; data area; set address; city = scan(add,-2); run; However it does rely on a lot of IFs and BUTs in terms of your dataset as already pointed out, hope it helps though.   ... View more

Assuming the seperators for the different parts of the address are consistent between observations then I would be tempted to use the scan function to write the last 'word' out to a new variable and then work with that. There does seem to be a lot of assumptions here however about likeness of the data between observations that would concern me. ... View more

It is possible to provide an extract of the dataset you have?   I think it might be preferrable to have a seperate variable for number, street name, city, region, country etc - that would make searching and creating new variables more straightforward.   At the moment I would be concerned that there is too much potential overlap between lines like   if index(address,"GERMANY")>0 THEN countyname_1='Mainland Europe'; and   if index(address,"NY")>0 THEN countyname_1='USA and Canada';   if they were ordered as above (i know they arent in your example) instances of 'Germany' could be allocated to 'USA and Canada'. Also is it possible for these strings to also appear in street names and so generate false results?   Maybe the data could be read in differently if the structure of the datasets does indeed turn out to be an issue? ... View more