About AJChamberlain

AJChamberlain · ‎07-17-2019

Thanks to everyone who's helped with this. Where I've got to is I am going to create a numeric variable with a numeric value for each distinct value in my original data. What I need now is to be able to show a comparison between the two variables clearly. So for example, if I have a table that includes this: Var 1 Var 2 abc123 1 abc123 1 abc123 1 def345 2 def345 2 def345 2 zxc334 3 zxc334 3 bnm567 4 bnm567 4 Then I want to be able to create a report, that looks like this, it's a bit like a cut-down Proc Freq: Var 1 Var 2 abc123 1 def345 2 zxc334 3 bnm567 4 Can anyone help me with a way to create this kind of report? Thanks A

AJChamberlain · ‎07-15-2019

Greetings SAS people, I have a dataset with a number of different alphanumeric variables in it, something like this: Record Seeded Country Centre - Candidate Task Language Code Section Date Month Year Criteria Score YearMonth Qtr Format Version Centre KeyMark 1 n United Arab Emirates AX001170389525345BPF W2 AC 0 74326428 17-Mar-18 3 2018 1 3 20183 1 CD 45R67FFD BC n 2 n United Arab Emirates AX001170389525345BPF W2 AC 0 74326428 17-Mar-18 3 2018 2 8 20183 1 CD 45R67FFD BC n 3 n United Arab Emirates AX001170389525345BPF W2 AC 0 74326428 17-Mar-18 3 2018 3 5 20183 1 CD 45R67FFD BC n 4 n United Arab Emirates AX001170389525345BPF W2 AC 0 74326428 17-Mar-18 3 2018 4 2 20183 1 CD 45R67FFD BC n 5 n United Arab Emirates AX001170389525345BPF W2 AC 0 74326428 17-Mar-18 3 2018 5 4 20183 1 CD 45R67FFD BC n 6 n United Arab Emirates AX001170389525345BPF W2 AC 0 74326428 17-Mar-18 3 2018 6 8 20183 1 PB 45R67FFD BC y 7 n United Arab Emirates AX001170389525345BPF W1 AC 0 74326428 17-Mar-18 3 2018 1 4 20183 1 PB 45R67FFD BC y 8 n United Arab Emirates AX001170389525345BPF W1 AC 0 74326428 17-Mar-18 3 2018 2 1 20183 1 PB 45R67FFD BC n 9 n United Arab Emirates AX001170389525345BPF W1 AC 0 74326428 17-Mar-18 3 2018 3 2 20183 1 PB 45R67FFD BC n 10 n United Arab Emirates AX001170389525345BPF W1 AC 0 74326428 17-Mar-18 3 2018 4 7 20183 1 PB 45R67FFD BC y This is a massive dataset (65m rows) and all the values change, sometimes there are only two different values, sometimes there are thousands. My task is to transform the data so that all of the content of every variable is numeric only. I could go through and identify all of the variations in data for each variable that is not already a numeric and change them all individually but that feels quite painful. Is there a clever way in SAS to transform a variable so that the transformed value is numeric only, and the system provides some kind of key to show what each original and transformed variable is? Note I am not trying to convert the formats here, it's the contents that need to change. Thanks A

AJChamberlain · ‎03-04-2019

So Astounding and @RW9 I'd like to explore this approach a bit further and chekc my understanding, so if I took the first three exam questions (items) and normalized the data in the way you suggest I assume I'd get this: Item Year Rating 1 2005 1 1 2007 -2 1 2008 2 1 2009 -1 1 2010 1 1 2011 0 1 2012 3 1 2013 -1 1 2014 1 1 2015 0 2 2005 -2 2 2007 1 2 2009 2 2 2010 1 2 2011 1 2 2012 1 2 2013 2 2 2014 1 2 2015 2 2 2016 0 3 2005 2 3 2007 1 3 2009 0 3 2010 0 3 2011 -1 3 2012 -2 3 2013 -2 3 2014 -1 3 2015 -2 3 2016 -3 3 2017 -4 3 2018 -4 I like this approach, I'd just need to re-format the data and work out how to apply my analyses from there. Thanks Andy

AJChamberlain · ‎03-04-2019

A good question Astounding! Basically, each of these coefficients, from 5 to -5 is an expression of how difficult an exam question is from the perspective of the candidates attempting it. The exam questions are used at different times over the period 2005 to 2018, sometimes every year over a period of years, sometimes with a gap of a year or more between each use. Some of our analysis needs to take account of the gaps between each use of the exam question (hence the first dataset with the years included) and some analysis needs to ignore the gaps and look only at the changes in coefficients each time the exam question is used (hence the second dataset), for this latter dataset I need to strip out the years when the question is not used. A

AJChamberlain · ‎03-04-2019

Thanks Novinosrin! Once again you have earned the "you've destroyed it" badge. Much appreciated A

AJChamberlain · ‎03-04-2019

Hi All I have a dataset with 15 records that holds data for some of the years between Year_2005 and Year_2018. Where data exists it is a number between -5 and 5. The table does not have a value for every record for every year: 2005 2006 2007 2008 2009 2010 2011 2012 2013 2014 2015 2016 2017 2018 1 -2 2 -1 1 0 3 -1 1 0 -2 1 2 1 1 1 2 1 2 0 2 1 0 0 -1 -2 -2 -1 -2 -3 -4 -4 -1 0 0 0 0 0 1 1 1 0 1 1 2 2 2 3 3 3 3 3 4 4 0 -1 -2 1 0 1 3 2 1 2 3 2 1 2 2 2 2 1 1 0 0 1 -1 0 1 1 1 1 2 2 2 1 1 0 1 0 0 0 0 1 1 1 0 -1 0 1 2 3 4 5 -4 -4 -3 -3 -4 -3 -3 -4 0 1 1 1 1 1 2 -1 -1 -2 -2 -1 0 -1 -1 -1 -1 -2 -1 0 1 -1 -1 -2 -1 -1 -1 1 I am trying to work out how to 'compress' this data so that the resultant dataset only records instances where there is an observation, this would mean that the records would have varying numbers of observations. So I want to disregard the year and get a dataset that looks like this: Obs1 Obs2 Obs3 Obs4 Obs5 Obs6 Obs7 Obs8 Obs9 Obs10 Obs11 Obs12 1 -2 2 -1 1 0 3 -1 1 0 -2 1 2 1 1 1 2 1 2 0 2 1 0 0 -1 -2 -2 -1 -2 -3 -4 -4 -1 0 0 0 0 0 1 1 1 0 1 1 2 2 2 3 3 3 3 3 4 4 0 -1 -2 1 0 1 3 2 1 2 3 2 1 2 2 2 2 1 1 0 0 1 -1 0 1 1 1 1 2 2 2 1 1 0 1 0 0 0 0 1 1 1 0 -1 0 1 2 3 4 5 -4 -4 -3 -3 -4 -3 -3 -4 0 1 1 1 1 1 2 -1 -1 -2 -2 -1 0 -1 -1 -1 -1 -2 -1 0 1 -1 -1 -2 -1 -1 -1 1 I've found all sorts of ways to compress or trim variables but I can't see how to do this, any ideas anyone? Thanks A

AJChamberlain · ‎02-22-2019

Hi All Here's another one from me, I hope is easy for you all - a coffee break problem to solve. I have three sets of variables: A1-A50 B1-B50 and Match1 to Match 50 I'm comparing A1 with B1, A2 with B2, and so on, and I want to run the simple process: If A1=B1 then Match1='Match' else Match1='Error' If A2=B2 then Match2='Match' else Match2='Error' and so on. Is there a way to put this into a Do loop? This must be possible I don't want to write this line of code 50 times! Assistance appreciated. A

AJChamberlain · ‎02-22-2019

Thanks Novinosrin, If you'd like some more fun I have another (probably straight forward) one for you. This is Do loops with IF/THEN I have three sets of variables: A1-A50 B1-B50 and Match1 to Match 50 I'm comparing A1 with B1, A2 with B2 and so on, and I want to run the simple process: If A1=B1 then Match1='Match' else Match1='Error' If A2=B2 then Match2='Match' else Match2='Error' and so on. Is there a way to put this into a Do loop? This must be possible I don't want to write this line of code 50 times! Happy to put this into the community as a formal post if you'd prefer Thanks again for your help A

AJChamberlain · ‎02-22-2019

Hi Jag I did but some unruly data stopped it working initially, the solution you suggest now does the job, thx for your help A

AJChamberlain · ‎02-22-2019

Thanks Paige

AJChamberlain · ‎02-22-2019

Hi Novinosrin, Yes they're all single $ - your solution worked after I tweaked my data slightly thx

AJChamberlain · ‎02-22-2019

I did try variations with 'of' but I had some issues with blanks in the variable, this has worked thanks. A

AJChamberlain · ‎02-22-2019

Hi guys, This can't be hard... but I am not finding a quick and clear solution. I want to concatenate a range of variables V1,V2,V3,V4...V18 with a single character '0' or '1' in each one into a new variable called RESULT, which will look, for example something like this: RESULT = '110101011010100111' I have 18 of these variables and Ireally don't want to use a format like: RESULT = cats(V1,V2,V3,V4, etc. etc) I don't want to strip out spaces or add commas or anything else, I just want to join the contents of all of the variables together. I am thinking the code must be something like: RESULT = cats(V1-V18); but I've tried variations on this to no avail. I am sure there's an easy answer, can anyone help me here please? Thanks A

AJChamberlain · ‎02-06-2019

Thanks for the useful thread A

AJChamberlain · ‎02-06-2019

Hi everyone I have a dataset in SAS with variables Var1 Var2 and Var3, Var1 is A or B Var2 is X or Y and for each combination of these I count the number of instances of Var 3. So this dataset: Var1 Var2 Var3 A X 1 A Y 1 A X 1 A X 1 B X 1 B X 1 B Y 1 B Y 1 Produces this summary: X Y A 3 1 B 2 2 but when there are no instances of some combinations I still want to report a null result as zero for all combinations, so for example this dataset: Var1 Var2 Var3 A X 1 A Y 1 A X 1 A X 1 A X 1 A X 1 A Y 1 A Y 1 produces this: X Y A 5 3 where I want it to produce this: X Y A 5 3 B 0 0 I'm using proc SQL to count my instances, but I need some way of telling the system to look out for all combinations of Var1 and Var2 and return 0 where they don't exist. Can anyone tell me how I might do this? Any ideas gratefully received! Thanks Andy

Online Status	Offline
Date Last Visited	‎08-05-2019 08:41 AM

Re: Converting the contents of variables with many different alpha-num...

Converting the contents of variables with many different alpha-numeric...

Re: Removing blank fields from a dataset in SAS and compressing the re...

Re: Removing blank fields from a dataset in SAS and compressing the re...

Re: Removing blank fields from a dataset in SAS and compressing the re...

Removing blank fields from a dataset in SAS and compressing the remain...

Do loops with IF/THEN statements

Re: Simple concatenation of string variables using CATS / CATX

Re: Simple concatenation of string variables using CATS / CATX

Re: Simple concatenation of string variables using CATS / CATX

Re: Removing blank fields from a dataset in SAS and compressing the re...

Re: Removing blank fields from a dataset in SAS and compressing the re...

Re: Removing blank fields from a dataset in SAS and compressing the re...

Re: Removing blank fields from a dataset in SAS and compressing the re...

Re: Removing blank fields from a dataset in SAS and compressing the re...

Re: Removing blank fields from a dataset in SAS and compressing the re...

Re: Converting the contents of variables with many different alpha-num...

Converting the contents of variables with many different alpha-numeric...

Re: Removing blank fields from a dataset in SAS and compressing the re...

Re: Removing blank fields from a dataset in SAS and compressing the re...

Re: Removing blank fields from a dataset in SAS and compressing the re...

Removing blank fields from a dataset in SAS and compressing the remain...

Do loops with IF/THEN statements

Re: Simple concatenation of string variables using CATS / CATX

Re: Simple concatenation of string variables using CATS / CATX

Re: Simple concatenation of string variables using CATS / CATX

Re: Simple concatenation of string variables using CATS / CATX

Re: Simple concatenation of string variables using CATS / CATX

Simple concatenation of string variables using CATS / CATX

Re: Returning a result of '0' for a combination of variables where no ...

Returning a result of '0' for a combination of variables where no inst...