About Dave13

Dave13 · ‎10-17-2023

@Patrick Thanks for the SQL code. I am unfortunately not very practiced with SQL, but will have to practice up. This example is very useful and will solve current and future issues. Thanks for all the help.

Dave13 · ‎10-16-2023

Yes, the input dataset has an AR1 PVALUE1 FDR1 for each combination. Sorry for the confusing data set examples. I've been trying to keep the example simple, and apparently things are confusing. I get a correct merge for the 1st line of each Swissprot, (sex=F, PND=2, Dose=0 condition) then missing values for the other combinations of sex, pnd, and dose. However, data exists for all of the other combinations of sex, pnd, and dose. I'll continue to work on it this afternoon myself. Thanks for your time and comments.

Dave13 · ‎10-16-2023

HI Patrick: The missing lines were left out for simplicity. They should be filled in with data. I manually cut/paste into an Excel spreadsheet to illustrate what I wanted. I'm including a small example of what I am currently getting. I'm not very proficient at SQL, but have a feeling I may need to improve to solve this issue. Thanks for looking into the question. Obs FILE SWISSPROT AR1 SEX PND DOSE PVALUE1 FDR1 _TYPE_ _FREQ_ MEAN_AR MED_AR N 1 F1 A0A096MJN4 0.323 F 2 0.00 0.00000 0.00000 0 6 0.32823 0.32258 6 2 A0A096MJN4 . F 8 0.00 . . 0 6 0.56312 0.57554 6 3 A0A096MJN4 . F 15 0.00 . . 0 6 0.67177 0.65325 6 4 A0A096MJN4 . F 2 3.78 . . 0 6 0.32472 0.29637 6 5 A0A096MJN4 . F 8 3.78 . . 0 6 0.47661 0.46189 6 6 A0A096MJN4 . F 15 3.78 . . 0 6 0.67148 0.69292 6 7 A0A096MJN4 . M 2 0.00 . . 0 6 0.32642 0.34287 6 8 A0A096MJN4 . M 8 0.00 . . 0 6 0.52017 0.52632 6 9 A0A096MJN4 . M 15 0.00 . . 0 6 0.62165 0.55316 6 10 A0A096MJN4 . M 2 3.78 . . 0 6 0.32390 0.33203 6 11 A0A096MJN4 . M 8 3.78 . . 0 6 0.50382 0.51008 6 12 A0A096MJN4 . M 15 3.78 . . 0 6 0.83233 0.86276 6 13 F1 A0A096MJZ0 1.214 F 2 0.00 0.98516 1.00000 0 6 1.18446 1.20836 6

Dave13 · ‎10-15-2023

Hello. I've been trying to merge several large data sets unsuccessfully. They can be rather large (3 million rows of data). I've been trying to sort the data on the common variables and merge without success. I'm only posting small examples of the data sets. Feel free to point me to reading/manuals to help resolve by coding Sample code: PROC SORT DATA=RATIO2; BY SWISSPROT SEX DOSE PND; RUN; PROC SORT DATA=PDMEANS2; BY SWISSPROT SEX DOSE PND; RUN; DATA SINGLE_PD; MERGE PDMEANS2 RATIO2; BY SWISSPROT SEX DOSE PND; RUN; PROC PRINT DATA=SINGLE_PD; The 1st data set looks like this: Obs SEX DOSE PND SWISSPROT _TYPE_ _FREQ_ MEAN_AR MED_AR N 1 F 0 2 A0A096MJN4 0 6 0.32823 0.32258 6 2 F 0 8 A0A096MJN4 0 6 0.56312 0.57554 6 3 F 0 15 A0A096MJN4 0 6 0.67177 0.65325 6 4 F 3.78 2 A0A096MJN4 0 6 0.32472 0.29637 6 5 F 3.78 8 A0A096MJN4 0 6 0.47661 0.46189 6 6 F 3.78 15 A0A096MJN4 0 6 0.67148 0.69292 6 7 M 0 2 A0A096MJN4 0 6 0.32642 0.34287 6 8 M 0 8 A0A096MJN4 0 6 0.52017 0.52632 6 9 M 0 15 A0A096MJN4 0 6 0.62165 0.55316 6 10 M 3.78 2 A0A096MJN4 0 6 0.3239 0.33203 6 11 M 3.78 8 A0A096MJN4 0 6 0.50382 0.51008 6 12 M 3.78 15 A0A096MJN4 0 6 0.83233 0.86276 6 13 F 0 2 A0A096MJZ0 0 6 1.18446 1.20836 6 The second data set looks like: Obs FILE SWISSPROT AR1 SEX PND DOSE PVALUE1 FDR1 1 F1 A0A096MJN4 0.323 F 2 0 0 0 2 F1 A0A096MJN4 0.575 F 8 0 8.38E-07 2.35E-06 3 F1 A0A096MJN4 0.652 F 15 0 0.002007 0.007018 Rows not included in post 97 F1 A0A096MJZ0 1.214 F 2 0 0.98516 1 What I'm trying to end up with: Obs SEX DOSE PND SWISSPROT _TYPE_ _FREQ_ MEAN_AR MED_AR N AR1 PVALUE1 FDR1 1 F 0 2 A0A096MJN4 0 6 0.32823 0.32258 6 0.323 0 0 2 F 0 8 A0A096MJN4 0 6 0.56312 0.57554 6 0.575 8.38E-07 2.35E-06 3 F 0 15 A0A096MJN4 0 6 0.67177 0.65325 6 0.652 0.002007 0.007018 4 F 3.78 2 A0A096MJN4 0 6 0.32472 0.29637 6 5 F 3.78 8 A0A096MJN4 0 6 0.47661 0.46189 6 6 F 3.78 15 A0A096MJN4 0 6 0.67148 0.69292 6 7 M 0 2 A0A096MJN4 0 6 0.32642 0.34287 6 8 M 0 8 A0A096MJN4 0 6 0.52017 0.52632 6 9 M 0 15 A0A096MJN4 0 6 0.62165 0.55316 6 10 M 3.78 2 A0A096MJN4 0 6 0.3239 0.33203 6 11 M 3.78 8 A0A096MJN4 0 6 0.50382 0.51008 6 12 M 3.78 15 A0A096MJN4 0 6 0.83233 0.86276 6 13 F 0 2 A0A096MJZ0 0 6 1.18446 1.20836 6 1.214 0.98516 1 14 F 0 8 A0A096MJZ0 0 6 1.27973 1.23149 6 Thaks in advance for any suggestions.

Dave13 · ‎12-06-2019

Yup, this is what I had in mind also. Just have to get existing data pulled together and cleaned up, so analysis can begin. Will have to circle back to this next week. Thanks Again!!

Dave13 · ‎12-06-2019

Thanks for the response. I had looked into web searches, and suspected SQL might be needed. Will need to purchase SQL manual and read up. The code posted by another person worked, so for now I can move forward with the project. Will clearly need to learn SQL, as I am sure this type of situation will come up again with this type of data. Greatly appreciate your time for the response.

Dave13 · ‎12-06-2019

This did exactly what I wanted. Thanks! Can see I need to play with this code to fully understand it, but at least I can move forward with the rest of the data cleanup/checking.

Dave13 · ‎12-05-2019

Please feel free to tell me to RTFM (just give friendly location). I am using SAS v9.4. I have a reasonably large data set, that I need to select certain rows to output to a new data set.I'm attaching a couple of examples. The variables are: Obs Grpelem Grindex Index Pfqmn Ffqmn Minfqmn Maxfqmn Bwmn Pkfqme Ffqme Minfqme Maxfwme Bwme Fmcthresh Sub Index1 1 2 23 1 39000 39000 38000 41000 2900 38600 38600 38400 39800 2200 . 8073F -2 2 2 25 1 70300 70300 64400 72200 7800 68100 68100 67500 69700 2600 . 8073F -2 3 2 25 2 44900 44900 43900 46800 2900 44800 44800 44000 46400 2600 23300 8073F -1 . . .17 2 148 1 38000 38000 36100 41000 4800 37900 37900 37900 39000 2100 . 8073F -2 18 2 178 1 37100 37100 35100 39000 3900 36200 36200 36100 37400 2300 . 8073F -2 19 4 183 1 37100 37100 35100 39000 3900 36800 36900 36600 37900 2200 . 8073F -2 20 3 201 1 36100 36100 35100 39000 3900 35900 35900 36200 36900 2000 . 8073F -2 21 3 201 2 67300 33600 66400 69300 2900 67300 44900 66400 69300 2900 9000 8073F -1 22 3 201 3 33200 33200 32200 35100 2900 33200 33200 32500 35000 2500 11700 8073F -1 I need to select the rows where Grindex has the same value in multiple rows (e.g. rows 2,3 + rows 20, 21, 22 in the example above). The actual number in the Grindex column will vary, but some rows will have repeats for each subject. These are the rows I need to put into a new data set for further processing. I can't provide any code, as this one has me stumped. The attempts I've made have failed as I can't figure out how to select multiple rows on a variable that will change multiple times within a subject and between subjects.

Online Status	Offline
Date Last Visited	‎12-08-2023 11:39 AM

Re: Merging/Interleaving data sets with multiple rows using SAS 9.4

Re: Merging/Interleaving data sets with multiple rows using SAS 9.4

Re: Merging/Interleaving data sets with multiple rows using SAS 9.4

Merging/Interleaving data sets with multiple rows using SAS 9.4

Re: Selecting certain rows for new data set

Re: Selecting certain rows for new data set

Re: Selecting certain rows for new data set

Selecting certain rows for new data set

Re: Selecting certain rows for new data set

Re: Merging/Interleaving data sets with multiple rows using SAS 9.4

Re: Merging/Interleaving data sets with multiple rows using SAS 9.4

Re: Merging/Interleaving data sets with multiple rows using SAS 9.4

Merging/Interleaving data sets with multiple rows using SAS 9.4

Re: Selecting certain rows for new data set

Re: Selecting certain rows for new data set

Re: Selecting certain rows for new data set

Selecting certain rows for new data set

SAS Analytics Explorers