About mmhxc5

mmhxc5 · ‎05-03-2019

Hi all, I have a SAS dataset of over 600,000 data points. A variable called Structure_Number_008 is the structure identifier. Some strucures has got Old_name and New_name both due to some rules and both names are available for some structures in the SAS data set. I have another excel file which has the Old_name and New_name correlation for all structures in two columns. In the SAS data set, I want to replace all Old_name with its New_name while keeping the structures which has only New_name. I would be grateful if anyone could help me to do the job. Thanks,

mmhxc5 · ‎02-08-2019

@Reeza, and @Ksharp, thank you for our time and help. Your codes both worked well - same result. @Reeza , ur code gives me this note in the log. NOTE: The query requires remerging summary statistics back with the original data. Thank you @PGStats for ur time.

mmhxc5 · ‎02-07-2019

@Reeza, here is a screenshot of my data from fhwaed2015 with STRUCNUM and EN and the other variables. I want to combine this with the other two datasets - fhwaed2016, and fhwaed2017 that is matching STRUCNUM and EN condition and generate the dataset to have all variables matching in STRUCNUM and EN. Thanks,

mmhxc5 · ‎02-07-2019

Hi all, I have three data sets and want to make another one to contain only observarions that is shared in all three datasets. The two variables that shall match the condition in all three datasets are STRUCNUM and EN such as this; STRUCNUM 12354 EN 410 from Fhwaed2015 STRUCNUM 12354 EN 410 from Fhwaed2016 STRUCNUM 12354 EN 410 from Fhwaed2017 If the above condition matched in all three datasets, I want to have them in my dataset. I tried the following code, but could not get the job done. I would appreciate if anyone could help me. proc sql; create table taxesdata as select * from data.Fhwaed2015, data.Fhwaed2016, data.Fhwaed2017 where Fhwaed2015.STRUCNUM=Fhwaed2016.STRUCNUM and Fhwaed2015.STRUCNUM=Fhwaed2017.STRUCNUM Fhwaed2015.en=Fhwaed2016.en and Fhwaed2015.en=Fhwaed2017.en ; quit; mmhxc5

mmhxc5 · ‎02-07-2019

@PGStats, thank you for your time and nice code. I would like to thank you @s_lassen , @Kurt_Bremser for your constructive comments and hints.

mmhxc5 · ‎02-06-2019

Hi all, I am using the following code to define new variables left_time and Right_time based on the condition for other variables STRUCTURE_NUMBER_008 and count, but the code does not give me the new variables. I would be glad if anyone could help me to get this fixed. data Ad_Year.Idaho_nbisteelic04; set Ad_Year.idaho_nbisteelic03; by STRUCTURE_NUMBER_008; if first.STRUCTURE_NUMBER_008 then left_Time=0 & Right_time=count; if last.STRUCTURE_NUMBER_008 then left_Time=count & Right_time=0; else Left_time= count & Right_time=count; run;

mmhxc5 · ‎02-03-2019

@Patrick, I need the Year column in each file, whereas it is only provided on the filename. Now I use the filename to add the Year column inside each file.

mmhxc5 · ‎02-03-2019

@Reeza, Yes, I added the macros for the other years. Here is the complete code I run, it works neatly, but only adds the Inspection_Year for 2017 and the other years are left blank. %macro runner(yy=); proc import datafile="E:\Desktop\Idaho\ID&yy..txt" dbms=dlm out=work.test; delimiter =","; getnames=yes; guessingrows=1000000; run; data Work.ID&yy.ay; set test; Inspection_year=&yy.; run; proc export data=Work.ID&yy.ay replace outfile = "E:\Desktop\Idaho\Ad_ear\ID&yy.txt" dbms=dlm; delimiter=','; run;proc datasets lib=work;delete test;quit; %mend runner; %runner(yy=1992); %runner(yy=1993); %runner(yy=1994); %runner(yy=1995); %runner(yy=1996); %runner(yy=1997); %runner(yy=1998); %runner(yy=1999); %runner(yy=2000); %runner(yy=2001); %runner(yy=2002); %runner(yy=2003); %runner(yy=2004); %runner(yy=2005); %runner(yy=2006); %runner(yy=2007); %runner(yy=2008); %runner(yy=2009); %runner(yy=2010); %runner(yy=2011); %runner(yy=2012); %runner(yy=2013); %runner(yy=2014); %runner(yy=2015); %runner(yy=2016); %runner(yy=2017);

mmhxc5 · ‎02-02-2019

@VDD, thank you for your nice code. I run the code, it works well, but it adds only the Inspection_Year for 2017. Other years are blank. Could you please look at the code and see if there is any error? I really appreciate it. Thank you!

mmhxc5 · ‎02-01-2019

@Reezathank you! Here is my code that is doing the job one by one. I want this code to be automated to all at once. proc import datafile='E:\Idaho\ID2017.txt' dbms=dlm out=work.test; delimiter =","; getnames=yes; guessingrows=100000; run; data Work.ID2017ay; set test; Inspection_year=2017; run; proc export data=Work.ID2017ay replace outfile = 'E:\Ad_Year\ID2017ay' dbms=dlm; delimiter=','; run;

mmhxc5 · ‎02-01-2019

Hi, I would like to post my question again, because the last time I did not get a satisfactry answer. I would like to add variable YEAR from 1992 to 2017 based on the filename for each file available in comma delimited .txt type. The file names are ID1992, ID1993, ..., ID2017,and export it back as its original file, that is .txt. Each file has 137 variables and more than 50,000 rows of data - character and numeric. I don't want to interfere within the files, but just want to add another variable YEAR for each row of each file. I appreciate your time and help. Thanks,

mmhxc5 · ‎01-30-2019

@ballardw, Thank you for asking more clarification. I am attaching the dataset and the following is an example for what I want. Based on the attached image, there are two values for SUPERSTRUCTURE_COND_059 of STRUCTURE_NUMBER_008 =00000000000H311. That is, SUPERSTRUCTURE_COND_059=9 which is repeated two times, and SUPERSTRUCTURE_COND_059=8 which is repeated 22 times as shown in Frequency Count column. I want to generate a variable called CENSOR to have a value of 1 for SUPERSTRUCTURE_COND_059=9 with Frequency_count =2, and CENSOR=0 for SUPERSTRUCTURE_COND_059=8 with Frequenency_count=22 becuase SUPERSTRUCTURE_COND_059=8 is the last observation for STRUCTURE_NUMBER_008=00000000000H311. Similarly, There are three values for SUPERSTRUCTURE_COND_059 of STRUCTURE_NUMBER_008=00000000000P403. That is, 6 which is repeated 13 times, 5 which is repeated 10 times, and 3 which is repeated 1 time as shown in Frequency_count column. Here, I want the variable CENSOR=1 for SUPERSTRUCTURE_COND_059=6 and SUPERSTRUCTURE_COND_059=5, and CENSOR =0 for SUPERSTRUCTURE_COND_059=3 because the SUPERSTRUCTURE_COND_059=3 is the last observation for STRUCTURE_NUMBER_008=00000000000P403. The attached is the dataset that I want to work with. Thanks, I have attached the file that I created the frequncy_column, but the original file is psc_post02 that I referred in my first post.

mmhxc5 · ‎01-30-2019

Hi dear programmers, I have a dataset of over 100,000 rows and three variables. The variables are; STRUCTURE_NUMBER_008 SUPERSTRUCTURE_COND_059, and Inspection_Year The SUPERSTRUCTURE_COND_059 gets value from 9 to 0 and any value can be repeated, STRUCTURE_NUMBER_008 is the ID for a bridge, and the inspecto_year is self explanatory and get value from 1992 to 2017. STRUCTURE_NUMBER_008 can enter and leave any time between 1992 to 2017. The dataset is sorted based on STRUCTURE_NUMBER_008 and Inspection_Year. I would like to generate a frequency table of STRUCTURE_NUMBER_008*SUPERSTRUCTURE_COND_059 condional to the following criteria. If the frequency STRUCTURE_NUMBER_008*SUPERSTRUCTURE_COND_059 counts any row except the last reading of STRUCTURE_NUMBER_008 and SUPERSTRUCTURE_COND_059 I want a variable call it CENSOR to be generated as 1, and if the frequency STRUCTURE_NUMBER_008*SUPERSTRUCTURE_COND_059 counts the last row of STRUCTURE_NUMBER_008 and SUPERSTRUCTURE_COND_059 the variable CENSOR to be generated as 0. I would be grateful of your time and help for writing a code to do the job. Please let me know if more explanation or if the data set is required. Thanks

mmhxc5 · ‎01-30-2019

@Tom , Thanks for your reply. I revised the code and added INPUT statement (with the trailing @), the code worked without any error. As I said in my first post, I want to add a column YEAR within each file based on its filename and export it as it is. Based on your code I get a data set combined as SAS dataset with variables I select from the .txt files. I don't want to interfere with the variables inside the files, I just want to add the column YEAR for each file and export it as .txt. Each file has 137 variables and more than 50,000 rows of data - character and numeric. I really appreciate your help.

mmhxc5 · ‎01-27-2019

@Tom, Thanks for correcting me. I got the code a little further, but still I get another error. Here is what I wrote. %Let path =E:\Desktop\Idaho; data want; length filename $200; infile "&path/ID*.txt" dsd filename=filename truncover; infile @; year = input(substr(scan(filename,-2,'./\'),3),4.); if filename ne lag(filename) then delete; input Inspection_Year; run; and the following is the error I get.

Online Status	Offline
Date Last Visited	‎02-08-2021 08:45 PM

How to plot only a specific stratum from proc PHREG

How to combine multiple select statements under one PROC SQL

merge by two variables produce zero ouput observation

Re: choosing a value conditional on two different variables

Re: choosing a value conditional on two different variables

choosing a value conditional on two different variables

Re: Remove a specific letter and only the number following it

Re: Remove a specific letter and only the number following it

Re: Remove a specific letter and only the number following it

Re: Remove a specific letter and only the number following it

Re: Remove a specific letter and only the number following it

Re: How to attach the second variable value to make the first variable...

Re: Printing programs in color

Re: how to get mean of each step of a step function

Re: Replace a variable name conditionally

How to use black and white fill patterns for bar graphs in ODS Graphic...

Replace a variable name conditionally

Replace a variable name conditionally

Re: combining data sets with only common variables

Re: combining data sets with only common variables

combining data sets with only common variables

Re: If else if then condition

If else if then condition

Re: Adding a variable based on the filename and exporting the file as ...

Re: Adding a variable based on the filename and exporting the file as ...

Re: Adding a variable based on the filename and exporting the file as ...

Re: Adding a variable based on the filename and exporting the file as ...

Adding a variable based on the filename and exporting the file as its ...

Re: Producing frequency table and a new variable conditional to the la...

Producing frequency table and a new variable conditional to the last r...

Re: How to add a variable column based on the filename

Re: How to add a variable column based on the filename