SAS Support Communities

twenty7 · ‎04-18-2024

Thank you!

twenty7 · ‎04-17-2024

hello, I have data that looks like this data have; input dated :date9. accid _f; format dated date9.; datalines; 01JAN2023 123 1 01FEB2023 123 1 01MAR2023 123 0 01APR2023 123 1 01MAY2023 123 0 ; run; there will be 1 record per account per month and the dataset has history going back a couple of years I need to add an additional column which states the date on which the event started (_f). for the above, the want would look like this data have; input dated :date9. accid _f start :date9.; format dated date9. start date9.; datalines; 01JAN2023 123 1 01JAN2023 01FEB2023 123 1 01JAN2023 01MAR2023 123 0 . 01APR2023 123 1 01APR2023 01MAY2023 123 0 . ; run; the start date would be the first time _f =1 and that should remain the same for each consecutive month where _f=1. The start date would be 'reset' when _f = 0. Any help on how to achieve this would be very much appreciated Thanks

twenty7 · ‎01-18-2024

I did, however my understanding is that this solution relies on knowing all of the potential values I ca also confirm this is no homework assignment!

twenty7 · ‎01-17-2024

whilst I do accept that the wider format is more difficult to work with it is needed in this instance as the data is to be exported and shared with non-technical users who need to be able to easily identify which records have a particular code

twenty7 · ‎01-17-2024

Hi all, I have a dataset which resembles the table below id cd_1 cd_2 cd_3 cd_4 cd_5 cd_6 1234 AB01 AX01 AZ01 1236 AY01 AB01 1239 AF01 AG01 XZ01 AA01 AX01 I want my code to add additional columns based on the values above so it looks something like the below id AB01 AX01 AZ01 AY01 FB01 AF01 AG01 XZ01 AA01 1234 1 1 1 0 0 0 0 0 0 1236 1 0 0 1 0 0 0 0 0 1239 0 1 0 0 0 1 1 1 1 I have created an array from columns cd_1-cd_99 but have no idea on which functions I would need to use to determine the value in the array position and then create a new column where the name is the array value and the contents is either 1 or 0 I want to avoid listing out all of the possible values for cd_1-cd_99 as there are around 30 but these will occasionally change Any help would be greatly appreciated Thank you

twenty7 · ‎02-24-2023

Thank you!!

twenty7 · ‎02-24-2023

Hi all, I have a dataset that currently looks like this acc_id curr ever 1 0 1 2 1 1 2 1 1 3 0 1 3 1 1 and I need to add a couple of additional columns to calculate a running total for each id - sum_curr and sum_ever acc_id curr ever sum_curr sum_ever 1 1 1 1 1 2 1 1 1 1 2 1 1 2 2 3 0 1 0 1 3 1 1 1 2 I'm struggling to work out how this would be done... any help would be greatly appreciated thanks 🙂

twenty7 · ‎02-03-2021

thanks once again @Tom

twenty7 · ‎02-03-2021

Hi, I'm trying do have a do loop within a macro but can't seem to get it to work the data I have... data have; input id $ severity_1 $ severity_2 $ severity_3 $; datalines; 001 WARNING N/A N/A 002 N/A N/A N/A 003 N/A WARNING N/A run; what I want to do... data want; set have; if severity_1 = "WARNING" or severity_2 = "WARNING" or severity_3 = "WARNING" then data_invalid = "Y"; else data_invalid = "N"; run; the reason I want a do loop is because there will be 100 severity columns what I have so far (that does not seem to work)... %macro data_invalid(); data want2; set have; %let i=1; %do %while (i < 3); %if severity_&i = "WARNING" %then data_invalid = "Y" %else data_invalid = "N" %end; run; %mend; %data_invalid(); proc freq data = want2; table data_invalid /missing; run; the error I get is "variable data_invalid not found" Help?

twenty7 · ‎01-25-2021

Thank you @Tom I did make a slight amendment to output the result into a new column data _null_ ; merge one two; by id ; array rules rule_: ; do index=1 to dim(rules); if rules[index] ne ' ' then do; if rules_match = "" and strip(vvaluex(rules[index])) ne 1 then rules_match = "differences" end; end; run; This would allow the user to easily isolate records where there is not a 100% match How would the reverse of this work where I need to identify records where there is a 1 but the code is not present in the array that is defined in the code above?

twenty7 · ‎01-23-2021

there can be unto 99 different codes that can appear and we do know the full list. the reason for the different columns was to highlight there could be rules which have not triggered yet in the data available

twenty7 · ‎01-23-2021

In the data I have, exists the following - Table 1 ID Rule_1 Rule_2 Rule_3 Rule_4 0001 PCODE_27 RCODE_1 RCODE_2 OCODE_1 0002 RCODE_2 I have the task of validating that the codes being returned are correct and this had led me to produce data which looks like this; Table 2a ID PCODE_27 RCODE_1 RCODE_2 OCODE_1 RCODE_21 0001 1 1 1 0 0 0002 0 0 1 0 0 '1' would indicate that rule was correct. I would like to create an array which would be populated for each record but only where the value is 1. My expectation is that it would end up looking something like this Table 2b ID EXP_CODE_1 EXP_CODE_2 EXP_CODE_3 EXP_CODE_4 EXP_CODE_5 0001 PCODE_27 RCODE_1 RCODE_2 0002 RCODE_2 This would then allow me to compare tables 1 and 2b directly I would like your advice as to whether I am correct in my approach and if so how would I go about creating the array Regards,

twenty7 · ‎10-28-2020

I need to be able to calculate the expenditure for each person on an application. The expenditure calculation is different based on the city in which the person resides. In the data I have available I am able to determine the number of persons on the application and the city. The maximum number of persons on an application is 3. the current solution I have is if a1_city = London then a1_affordability_calc = 'a' else if a1_city in (Manchester, Liverpool) then a1_affordability_calc = 'b' else if a1_city in (Birmingham) then a1_affordability_calc = 'c' else if a1_city in (Glasgow) then a1_affordability_calc = 'd' else a1_affordability_calc = 'e'; if a1_affordability_calc = 'a' then a1_calc_result = (x+y+z)*0.25; if a1_affordability_calc = 'b' then a1_calc_result = (x+y+z)*0.10; if a1_affordability_calc = 'c' then a1_calc_result = (x+y+z)*0.21; if a1_affordability_calc = 'd' then a1_calc_result = (x+y+z)*0.23; if a1_affordability_calc = 'e' then a1_calc_result = (x+y+z)*0.20; /*REPEAT ABOVE REPLACING THE PREFIX a1 with a2 and a3*/ The calculations will be a lot more complicated but hopefully this explains the problem better What I wanted to avoid in my potential solution is repeating the same section of code with the calculations for each of the 3 potential applicants.

twenty7 · ‎10-28-2020

so essentially I want to perform a different calculation based on the city in which that particular applicant resides. Currently there are only two calculations - one for 'London' and for 'not London' but eventually there could be a calculation for each city. Due to the number of potential calculations I wanted to be able code each calculation once and then loop through it for each applicant.

twenty7 · ‎10-28-2020

I have a dataset on which I am going to be performing a calculation. The calculation will need to be performed for each individual. The structure of the data is similar to data app; input ID $ A1_CITY $ A2_CITY & NUM_OF_CUSTS datalines; 001 LONDON LONDON 2 002 LONDON LEEDS 2 003 MANCHESTER LONDON 2 004 LEEDS LONDON 2 ; run; As the calculation is quite long I was thinking of using a do loop within a macro for this. This is what I have come up with... data calc; set app; %let I=1; %macro calc; %do %while (&I <= NUM_OF_CUSTS); %if A&i._CITY = "LONDON" %THEN LONDON = "Y"; %else LONDON = "N" /*rest of calc here*/ /*%if London = "Y" %then... %else...*/ %let I = %eval(&I+1); %end; %mend; run; %calc; the statement %do %while (&I <= NUM_OF_CUSTS); also isn't being resolved as expected for some reason Any help as to where I'm going wrong would be greatly appreciated

Online Status	Offline
Date Last Visited	‎04-18-2024 12:26 PM

SAS Support Communities

Re: Determining the start date

Determining the start date

Re: create new columns based on array values

Re: create new columns based on array values

create new columns based on array values

Re: Running Totals

Running Totals

Re: Macro Do Loop

Macro Do Loop

Re: Arrays?

Re: create new columns based on array values

Re: create new columns based on array values

Re: Arrays?

Re: Arrays?

Re: DateTime Macro Variable

call symput with a do loop

Re: Determining the start date

Determining the start date

Re: create new columns based on array values

Re: create new columns based on array values

create new columns based on array values

Re: Running Totals

Running Totals

Re: Macro Do Loop

Macro Do Loop

Re: Arrays?

Re: Arrays?

Arrays?

Re: SAS DO Loop Macro

Re: SAS DO Loop Macro

SAS DO Loop Macro

Follow Us

What is...